SQL Puzzle #1 - Answer
Here's my solution to the puzzle in my previous post. If you haven't seen the challenge, go back and read that first.
Are you reading this before trying to solve the puzzle on your own? Unacceptable! Your IP is being logged! C'mon, give it a try!
My Solution
I used 29 lines and 3 temp tables. It's reasonably straightforward - I set up a rank table (#temp), cross join it with the Student table to get ten rows for each student (a cartesian product), then join it to the grades table in order and select out the result.
The one little trick is the ranking in the selct for #temp3 - I used the GradeID as the tie-breaker if the student gets the same grade more than once. Without a tie-breaker, a duplicate score would result duplicating one Row number and skipping the next (e.g. 1,2,3,4,5,5,7,8,9,10).
How would you solve this?
use [test]
go
IF OBJECT_ID('tempdb..#temp') IS NOT NULL drop table #temp
create table #temp (Row int)
declare @counter int
set @counter = 0
while @counter < 10
begin
set @counter = @counter + 1
insert into #temp (Row) values (@counter)
end
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL drop table #temp2
create table #temp2 (Row int, StudentID int)
insert into #temp2 select Row, StudentID from Student, #temp order by StudentID, Row
IF OBJECT_ID('tempdb..#temp3') IS NOT NULL drop table #temp3
select *, (SELECT COUNT(*) FROM Grade pt2 WHERE pt2.Grade + (.001*pt2.GradeID) >= pt.Grade + (.001*pt.GradeID) AND pt2.StudentID = pt.StudentID) AS Row
into #temp3
from Grade pt order by StudentID, Grade desc
select a.Row, a.StudentID, s.FirstName, s.LastName, b.Grade from
#temp2 a left join #temp3 b on a.StudentID = b.StudentID and a.Row = b.Row
inner join Student s on a.StudentID = s.StudentID
drop table #temp
drop table #temp2
drop table #temp3