# 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

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]goif exists (select * from information_schema.tables where table_name = 'Grade') drop table Gradeif exists (select * from information_schema.tables where table_name = 'Student') drop table studentcreate 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 intdeclare @grade intset @counter = 0while @counter < 20begin  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)endselect * from Gradego`

• The answer would be very simple, if you wouldn't have the rule to have always 10 rows, even when less than 10 grades are known.

Because you can do something like:
SELECT S.StudentID, S.FirstName, S.LastName, SG.Grade, FROM
STUDENT S LEFT JOIN (SELECT TOP 10 * FROM GRADE G WHERE S.StudentID = G.StudentID ORDER BY Grade DESC) SG
ORDER BY S.StudentID ASC

(not tested, might be full of bugs)

You see, the ordering number in the list and the empty rows to make it to 10 rows, is not a set problem, but a data consumer's problem, so you shouldn't solve it in SQL but in the code consuming the data.

• Sorry, Frans - My puzzle, my rules. You flunk! ;-)

• This seems to work for me:

select
d.studentid
,s.firstname
,s.lastname

from (
select
,s.studentid
,null as rank
from
join student s
on s.studentid=g.studentid
where
union select null, studentid,1 from student s where 1 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,2 from student s where 2 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,3 from student s where 3 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,4 from student s where 4 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,5 from student s where 5 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,6 from student s where 6 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,7 from student s where 7 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,8 from student s where 8 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,9 from student s where 9 > (select count(*) from grade t where s.studentid=t.studentid)
union select null, studentid,10 from student s where 10 > (select count(*) from grade t where s.studentid=t.studentid)
) d
left join student s
on d.studentid=s.studentid
order by

• @Scott - After thinking about it a bit, I still like my solution better. They're comparable in terms of the amount of code, but mine's more dynamic. If I needed to show the top 100 grades for each student, I'd only have to change one character; you'd have more work to do. You can generate the SQL, but it's still more work.

• SQL Server 2005 Rulez :)

SELECT *
FROM [dbo].[Student] s
CROSS APPLY (
SELECT TOP 10 *
FROM (
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
) g1
) g2

• Jon,

I agree that your solution is far more maintainable, and that is a major advantage for taking that approach. But, I often favor performance over maintainability when coding. I checked the three above approaches against a larger recordset (120 grades, 14 students) to get a feel for their performance in SQL Profiler. Here were my results on SQL 2005:

First Run:
Jon: 71ms, 1249 reads, 47 cpu
Me: 15ms, 1327 reads, 16 cpu
sacode: 7ms, 52 reads, 15 cpu

Second Run: (comparable results each subsequent time)
Jon: 41ms, 1203 reads, 32 cpu
Me: 16ms, 1327 reads, 15 cpu
sacode: 1ms, 44 reads, 0 cpu

wow. I think sacode kicked our butts. And the principle on that approach - unioning nulls w/ no subqueries then selecting top 10 in the outer query - was definitely optimum (I was kicking kicking my own rear for not thinking of that). And, I think the only SQL 2005 feature used (Cross Apply) could easily be represented by some creative joins in SQL 2000 (would probably reduce performance slightly though)

Cool puzzle, I'd love to see more in the future.

Scott

• **REALLY LATE ENTRY**

Modifying Sacode's salution just a bit:

select *
from dbo.student s
cross apply
(
select top 10 *
from
(
where g.stid = s.stid

union all

select null
where g.stid = s.stid
) g1
) g2

from student a cross join (select 1 as a union select 2 as a union select 3 as a union select 4 as a union select 5 as a union select 6 as a union select 7 as a union select 8 as a union select 9 as a union select 10 as a) x
left outer join
where a.studentid = b.studentid
on a.studentid = b.studentid

• SELECT
FROM
(
SELECT Row_Number() OVER ( PARTITION BY Student.StudentId ORDER BY Student.StudentId) as StudentRow, StudentId,FirstName,LastName
FROM Student CROSS JOIN (SELECT TOP 10 GradeId FROM Grade ) InnerQuery
) Students
LEFT JOIN
(

• Wow. I think Imran Muhammad Ali did a fantasic job by comming up with a great query. It really works. Cheers.

• Just saw puzzle while surfing around thought of giving it a try

(
Select number,ID,X.Name from master..spt_values,
(
Select 1 ID,'Smith' Name
Union
Select 2 ID,'John' Name
) X
where type='p'
and number between 1 and 10
)X1
Left Join
(
(
Select 1 ID,'Smith' Name
Union
Select 2 ID,'John' Name
)St
Inner Join
(
Select ROW_NUMBER() over(partition by ID order by Grade desc) RNum,
(
union All
union All
union All
union All
union All
union All
union All
union All
union All
union All
union All
union All
union All
)X
)X2 on X1.ID = X2.ID and X1.Name = X2.Name And X1.number = X2.RNum

Hope it is working as expected

• select ROW_NUMBER() over (partition by s.studentid order by s.studentid) as Rows,
s.*,g.grade,ROW_NUMBER() over (partition by s.studentid order by s.studentid) as Row
from STUDENT s
INNER JOIN
on g.Studentid=s.studentid

• Hey Guys !!!

Got you the solution !!!

declare @row table(id int)
insert into @row values(1)
insert into @row values(2)
insert into @row values(3)
insert into @row values(4)
insert into @row values(5)
insert into @row values(6)
insert into @row values(7)
insert into @row values(8)
insert into @row values(9)
insert into @row values(10)
select * from
(
select
row_number() over (partition by s.studentid order by s.studentid) as row, s.studentid, s.firstname,
from student as s
on s.studentid = g.studentid
union
select
a.id + b.cnt as row, b.std_id, b.fname, b.lname, NULL grade
from
(
select
s.studentid std_id, min(s.firstname) fname, min(s.lastname) lname, count('*') cnt,
10 - count('*') as remain
from student as s inner join grade as g
on s.studentid = g.studentid
group by s.studentid
) as b
cross join
@row as a
where a.id <= b.remain
) as a
order by a.studentid, a.row

• Hi...
This is my attempt

declare @CountSid as int
declare @i as int
declare @StrFinal as Varchar(max)
declare @Str1 as Varchar(max)

set @CountSid=0
set @i=1
set @Str1=' '
set @StrFinal=' '

select @CountSid=COUNT(distinct StudentID) from Student

while(@i<=@CountSid)
Begin

set @Str1=
'SELECT Row_Number() Over(Order By SG.Grade desc) As Row, S.StudentID, S.FirstName, S.LastName, Case When SG.Grade<10 Then Null Else Cast( SG.Grade As Varchar(10)) End Grade
FROM STUDENT S Inner JOIN (SELECT distinct top 10 StudentID,Grade FROM GRADE G where StudentID='+' '+Cast( @i As Varchar(10))+' ' +'order by Grade desc) SG On S.StudentID=SG.StudentID'

if @i3
begin
set @StrFinal=@StrFinal+' '+@Str1+' '+'Union All'+' '
end
else
begin
set @StrFinal=@StrFinal+' '+@Str1+' '
end
set @i=@i+1
End

exec(@StrFinal)

Comments have been disabled for this content.