Geek asks and answers

How to return a single row based on a collection of more than one column

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

Let's 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 string must be a maximum rank and a maximum date. So, for FkId "A" the query will return a string for "ID3".

I managed to return one row using subqueries; first I get MAX (Rank), then I join another request that gets the MAX (Date) group by FkId and 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 watching.

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

+5
source share
4

ROW_NUMBER CTE ( 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
+4

( ) :

  • FkId
  • .
  • .

, FkIds , , .

(3), GROUP BY:

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

. FkId, :

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

sqlfiddle, .

+2

Rank() .


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

+1

You can also covertly and accomplish what is suggested like this :

select top 1 with ties *
from TableA
order by rank() over (
  partition by FKID
  order by [Rank] desc, [Date] desc
)
0
source

More articles:


All Articles