sql server 2008 - SQL Select, Specific Rows based on multiple conditions?

Question

Not sure if it's because I'm tired, but I can't seem to figure this out...

I'm looking for a Query that will filter the data based on a couple items...

Sample Data:

Business_Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/13/13 17:49
1/1/2013 0:00   119118  284 6   3/20/13 13:03
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:03
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/13/13 17:49
1/1/2013 0:00   120327  255 8   3/20/13 13:03
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:03
3/1/2013 0:00   120327  13  2   3/20/13 13:25

What I would like returned, is one line, Per Business month, for each unique ID with the highest Loaded Date...

Sample Output:

Business Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:25

I've tried different things, but can't seem to figure it out... I'm using MS SQL Server...

Hopefully someone can help.

Thanks!

Answer 1

;WITH x AS 
(
  SELECT [Business_Month], ID, Calls, Transferred, Loaded,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY ID, [Business Month] ORDER BY Loaded DESC)
  FROM dbo.yourtable
)
SELECT [Business Month], ID, Calls, Transferred, Loaded
FROM x
WHERE rn = 1
ORDER BY ID, [Business Month];