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

14 Comments

  • 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
    ,g.grade

    from (
    select
    g.gradeid
    ,s.studentid
    ,null as rank
    from
    grade g
    join student s
    on s.studentid=g.studentid
    where
    gradeid in (select top 10 gradeid from grade where studentid=g.studentid order by grade desc)
    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 grade g
    on d.gradeid=g.gradeid
    left join student s
    on d.studentid=s.studentid
    order by
    studentid asc, grade desc

  • @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 (
    SELECT g.[Grade] FROM [dbo].[Grade] g WHERE g.[StudentID] = s.[StudentID]
    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
    ORDER BY g1.[Grade] DESC
    ) 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
    (
    select g.grade
    from dbo.grade g
    where g.stid = s.stid

    union all

    select null
    from dbo.grade g
    where g.stid = s.stid
    ) g1
    order by g1.grade desc
    ) g2

  • select a.studentid,a.firstname,a.lastname,b.grade
    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
    (select a.studentid,a.gradeid,a.grade,count(b.grade) as grade_rank
    from grade a, grade b
    where a.studentid = b.studentid
    and a.grade>= b.grade
    group by a.studentid,a.gradeid,a.grade) b
    on a.studentid = b.studentid
    and x.a = b.grade_rank
    order by a.firstname,a.lastname, b.grade desc

  • SELECT
    FirstName, LastName,Grade
    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
    (
    SELECT ROW_NUMBER() OVER ( PARTITION BY Grade.Studentid ORDER BY Grade.Studentid) as GradeRow,StudentId,Grade FROM Grade
    ) Grades
    ON Students.StudentRow= Grades.GradeRow AND Students.StudentId = Grades.Studentid

  • 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 X1.*,X2.Grade from
    (
    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 ST.ID,ST.name,Grades.Grade,Grades.RNum from
    (
    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,
    X.ID,X.grade from
    (
    Select 1 ID,90 Grade
    union All
    Select 1 ID,89 Grade
    union All
    Select 1 ID,79 Grade
    union All
    Select 2 ID,75 Grade
    union All
    Select 2 ID,88 Grade
    union All
    Select 2 ID,7 Grade
    union All
    Select 2 ID,5 Grade
    union All
    Select 2 ID,15 Grade
    union All
    Select 2 ID,25 Grade
    union All
    Select 2 ID,45 Grade
    union All
    Select 2 ID,77 Grade
    union All
    Select 2 ID,97 Grade
    union All
    Select 2 ID,67 Grade
    union All
    Select 2 ID,45 Grade
    )X
    ) Grades on St.ID=Grades.ID and Rnum between 1 and 10
    )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
    GRADE g
    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,
    s.lastname, g.grade
    from student as s
    inner join grade as g
    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.