0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

WITH A As (
SELECT 1 as ID, 100 rank , 'Name1' as Name
UNION SELECT 1,    45,  'Name2'
UNION SELECT 2,    60,  'Name3'
UNION SELECT 2,    42,  'Name4'
UNION SELECT 2,    88,  'Name5' )
, B AS (
SELECT 1 as ID, 'fn1' as FileName
UNION SELECT 2  , 'fn2')
, CTE AS 
(
   SELECT ID, 
          RANK,
          Name,
          ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RANK DESC) rn
   FROM A
)
SELECT b.Id , b.FileName,cte.Name
FROM
   b
   JOIN cte 
   ON b.id = cte.id
      and cte.rn = 1

fork query

edited feb 22 anonymous

created feb 27 12 conradfrix