CODEWITHSUNDEEP
sql-server-2008
Mr John
Mr John

Reputation: 241

SQL Server 2008 -Joining on Max Values on Multiple tables

I have the below two tables. 

a.id    a.UpdateDate    a.O    a.1     b.id    b.UpdateDate  b.0   b.1
  1        1/1/15        a      b         1         2/1/15    a     b
  1        1/1/15        c      d         1         3/1/15    a     c
  1        5/1/15        e      f         1         3/1/15    d     e

I would like to see one row per Id. The lastest Update Date for Table a The Latest Updatedate for table b then join those two tables on Id. That way I'm getting the most recent Updates from both tables with one row per id. 

a.id    a.UpdateDate   b.id    b.UpdateDate  a.o   a.1   b.0   b.1
  1        5/1/15       1         3/1/15      e     f     d     e

I want the max values for both tables and struggling on next steps. EDIT --I need the latest UpdateDate and id (1 row Per id) for both tables, then I would like to join table a and b to id.

Select a.id, a.UpdateDate, b.id, b.UpdateDate  
From a
  inner join (select id, max(UpdateDate) AS dt From a group by id) 
   maxdt On A.id = maxdt.id and a.UpdateDate = maxdt.UpdateDate

The above solves extracting the max UpdateDate from table a. Now how would I extract the max UpdateDate from table b? I hope that this is clear...

Upvotes: 0

Views: 47

Answers (1)

Quassnoi
Quassnoi

Reputation: 425683

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY updateDate DESC) rn
        FROM    a
        GROUP BY
                id
        ) ag
JOIN    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY updateDate DESC) rn
        FROM    b
        GROUP BY
                id
        ) bg
ON      bg.id = ag.id
WHERE   ag.rn = 1
        AND bg.rn = 1

Upvotes: 1

Related Questions