SQL Server query grouped by max value in column

Question

**Update:

using the Rank() over partition syntax available in MS SQL Server 2005 does indeed point me in the right direction, it (or maybe I should write "I") is unable to give me the results I need without resorting to enumerating rows in code.

For example, if we select TOP (1) of rank, I get only one value, ie., slot 1. If I use MAX(), then I get the top ranked value for each slot...which, in my case, doesn't work, because if slot 2's top value is NULL, but it's next to MAX value is non-empty, that is the one I want.

So, unable to find a completely T-SQL solution, I've resorted to filtering as much as possible in SQL and then enumerating the results in code on the client side.

Original:

I've been hitting advanced T-SQL books, StackOverflow and google trying to figure out how to handle this query either by using pivots or by using analytic functions. So far, I haven't hit on the right combination.

I have schedules that are ranked (higher value, greater precedence). Each schedule has a playlist of a certain number of numbered slots with files.

What I need to do, is line up all the schedules and their associated playlists, and for each slot, grab the file from the schedule having the highest ranking value.

so, if I had a query for a specific customer with a join between the playlists and the schedules, ordered by Schedule.Rank DESC like so:

PlaylistId   Schedule.Rank    SlotNumber    FileId
100               100             1          1001
100               100             2          NULL
100               100             3          NULL
200                80             1          1101
200                80             2          NULL 
200                80             3          NULL
300                60             1          1201
300                60             2          NULL
300                60             3          2202
400                20             1          1301
400                20             2          2301
400                20             3          NULL

From this, I need to find the FileId for the MAX ranked row per slotnumber:

SlotNumber   FileId    Schedule.Rank
1             1001         100
2             2301          20
3             2202          60

Any ideas on how to do this?

Table Definitions below:

CREATE TABLE dbo.Playlists(
    id int NOT NULL)

CREATE TABLE dbo.Customers(
    id int NOT NULL,
    name nchar(10) NULL)

CREATE TABLE dbo.Schedules(
    id int NOT NULL,
    rank int NOT NULL,
    playlistid int NULL,
    customerid int NULL)

CREATE TABLE dbo.PlaylistSlots(
    id int NOT NULL,
    slotnumber int NOT NULL,
    playlistid int NULL,
    fileid int NULL)

Answer 1

Have you looked at SQL Server's (2005 onwards) PARTITION and RANK features?

Answer 2

SELECT slotnumber, fileid, rank
FROM Schedules
INNER JOIN PlaylistSlots
  ON Schedules.playlistid = PlaylistSlots.playlistid
WHERE
 RANK() OVER (PARTITION BY slotnumber ORDER BY rank DESC) = 1

Answer 3

select SlotNumber, FileId, ScheduleRank
FROM intermediateTable a, 
(
   SELECT SlotNumber, Max(Schedule.Rank) as MaxRank
   FROM intermediateTable O
   WHERE FileId is not null GROUP BY SlotNumber) b
WHERE b.SlotNumber = a.SlotNumber and b.MaxRank = a.Rank

This query uses the intermediate output to build the final output.
Does this help?