SQL Puzzle #1
I had to write a semi-interesting SQL query this past week and thought it might make for a fun SQL puzzle (for very small values of "fun").
I'm working on a bio-tech business intelligence application, but I simplified things way down to two tables in a SQL Server 2000 database: Student and Grade. The student may have any number of grades, and may of course may get the same grade several times.
Prepare a report that shows the top ten grades for each student. You must show exactly ten rows for each student, so if they have less than ten grades you should show a null. Remember that you need to handle the case where the same student gets the same score more than once (e.g.- in my results below, check out Bobby Brown's top two grades are both 82).
My answer will not use cursors and will run on standard SQL 2000 T-SQL, but if you'd like to submit answers using the SQL 2005 ranking functions, go for it. Post your answers as comments; my answer will be in the next post.
Here's a SQL script with some sample data. You'll need to create a "test" database to run this.
if exists (select * from information_schema.tables where table_name = 'Grade') drop table Grade
if exists (select * from information_schema.tables where table_name = 'Student') drop table student
create table Student(
StudentID int identity(1,1) primary key,
insert into student(firstname, lastname) values ('Bill','Smith')
insert into student(firstname, lastname) values ('Bobby','Brown')
insert into student(firstname, lastname) values ('Derek','Zoolander')
create table Grade(
GradeID int identity(1,1),
ALTER TABLE Grade WITH CHECK ADD CONSTRAINT FK_Grade_Student FOREIGN KEY([StudentID]) REFERENCES Student(StudentID)
declare @counter int
declare @grade int
set @counter = 0
while @counter < 20
set @counter = @counter + 1
set @grade = cast(50*rand(cast(cast(newid() as binary(8)) as int)) as int) + 50
insert into Grade (StudentID,Grade) values (@counter % 3 + 1,@grade)
select * from Grade