How do I return a single row based on the aggregate of more than one column

Question

Sorry for the ambiguous title, not sure how to search, or ask this question.

Lets say we have TableA:

RowID        FkId        Rank        Date
ID1          A           1           2013-3-1
ID2          A           2           2013-3-2
ID3          A           2           2013-3-3
ID4          B           3           2013-3-4
ID5          A           1           2013-3-5

I need to create a view, that will return 1 row for each FkId. The row should be the max rank, and max date. So for FkId "A", the query would return the row for "ID3".

I was able to return a single row by using sub-queries; first I get the MAX(Rank), then join to another query that gets MAX(Date) group by FkId & Rank.

SELECT TableA.*
(Select FkId, MAX(Rank) AS Rank FROM TableA GROUP BY FkId) s1 
INNER JOIN (Select FkId, Rank, MAX(Date) AS Date FROM TableA GROUP BY FkId,Rank) s2 ON s1.FkId = s2.FkId AND s1.Rank = s2.Rank 
INNER JOIN TableA ON s2.FkId = TableA.FkId AND s2.Rank = TableA.Rank AND s2.Date = TableA.Date

Is there a more efficient query that would achieve the same results? Thanks for looking.

Edit: Added ID5 since the last answer. If I tried a normal MAX(rank),MAX(Date) GROUP BY FkId, then for "A", I would get A; 2; 2013-3-5. This result would not match up to a RowId.

Answer 1

You can use ROW_NUMBER with a CTE (presuming sql-server >= 2005):

WITH CTE AS
(
   SELECT TableA.*,
      RN = ROW_NUMBER() OVER (PARTITION BY FkId Order By Rank Desc, Date DESC)
   FROM Table A
)
SELECT RowID,FkId, Rank,Date
FROM CTE WHERE RN = 1

Answer 2

Your question (clarified in the comments to this answer) asks for:

1. A single row for each FkId
2. The Max Date and Rank
3. The results to correspond to a row in the original data.

In the case that there are FkIds with rows such that the maximum date and maximum rank are in separate rows, you'll have to relax at least one of these requirements.

If you're willing to relax requirement (3), then you can use GROUP BY:

SELECT FkId, MAX(Rank) AS Rank, Max(Date) AS Date
FROM TableA
GROUP BY FkId

Given the extra information in the comments. That you want the latest, of the highest ranked entries for each FkId, the following should work:

SELECT FkId, Rank, MAX(Date) AS Date
FROM TableA A
WHERE Rank = (SELECT MAX(Rank) 
              FROM TableA sub 
              WHERE A.FkId = sub.FkId 
              GROUP BY sub.FkId)
GROUP BY FkId, Rank

Here's a sqlfiddle to show it in action.

Answer 3

You can use Rank() and inline query to achieve it.

select * from TableA
where RowID in (
      select rowID from (
           select FKID, RowID, 
                  rank() over (partition by FKID order by [Rank] desc, [Date] desc) as RankNumber
                  from TableA ) A
      where A.RankNumber=1 ) 

SQL Fiddle Demo