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

3 Comments

  • Bit of a cheat using lots of sub-queries, but it does eliminate the temporary tables, or at least two of them anyway...

    USE [Test]
    GO

    IF OBJECT_ID('tempdb..#temp1') IS NOT NULL drop table #temp1
    CREATE TABLE #temp1 (row INT)

    DECLARE @counter INT
    SET @counter = 0
    WHILE @counter = pt.Grade + (.001*pt.GradeID) AND pt2.StudentID = pt.StudentID) AS Row from Grade pt) SQ1
    ON T.StudentID = SQ1.StudentID AND T.row = SQ1.row

    drop table #temp1

  • why not table variables?

  • I used temp tables rather than table variables or subqueries because I think it's a little easier to follow what's going on. You can comment out the "delete #temp" lines at the end and select the values to see what's going on.

    @haacked - So far it looks like Rob is the only one that passes, although I'm suspicious that he might have referenced the solution when he was writing his query. ;-)

Comments have been disabled for this content.