SQL Puzzle #1
Background
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.
| StudentID | FirstName | LastName | | 1 | Bill | Smith | | 2 | Bobby | Brown | | 3 | Derek | Zoolander | | | GradeID | StudentID | Grade | | 1 | 2 | 61 | | 2 | 3 | 98 | | 3 | 1 | 87 | | ... | ... | ... | | 18 | 1 | 58 | | 19 | 2 | 82 | | 20 | 3 | 68 | |
Task
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).
| Row | StudentID | FirstName | LastName | Grade |
| 1 | 1 | Bill | Smith | 94 |
| 2 | 1 | Bill | Smith | 87 |
| 3 | 1 | Bill | Smith | 82 |
| 4 | 1 | Bill | Smith | 67 |
| 5 | 1 | Bill | Smith | 62 |
| 6 | 1 | Bill | Smith | 58 |
| 7 | 1 | Bill | Smith | NULL |
| 8 | 1 | Bill | Smith | NULL |
| 9 | 1 | Bill | Smith | NULL |
| 10 | 1 | Bill | Smith | NULL |
| 1 | 2 | Bobby | Brown | 82 |
| 2 | 2 | Bobby | Brown | 82 |
| 3 | 2 | Bobby | Brown | 72 |
| … | … | … | … | … |
| 7 | 3 | Derek | Zoolander | 53 |
| 8 | 3 | Derek | Zoolander | NULL |
| 9 | 3 | Derek | Zoolander | NULL |
| 10 | 3 | Derek | Zoolander | NULL |
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.
use [test]
go
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,
FirstName varchar(50),
LastName varchar(50)
)
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),
StudentID int,
Grade float
)
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
begin
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)
end
select * from Grade
go