TSQL Query to fetch row details of a table having the maximum value for a column

I have two tables.

Table 1

Num 
1
2
3

Table 2

Num   Date    Amount
1     12/31     30
1     12/30     31
1     12/29     20
2     12/31     100
2     12/30     90
3     12/31     12
4     11/1       1

Now my result should have

   Num   Date    Amount
    1    12/31     30
    2    12/31     100 
    3    12/31     12

(for the 'Num' values in table 1, join with table2 where the date is the most recent)

I am t开发者_如何转开发rying to write a tsql query to achieve this.

Any help is appreciated. Thanks

If you want the most recent date for each table1 num individually:

with maxdates as (
    select  T1.num, max(T2.date) as date
      from  table2 T2
      join  table1 T1
        on  T2.num = t1.num
    group by T1.num
)
select  t2.num, t2.date, t2.amount
  from  table2 T2
  join  maxdates M
    on  T2.num = M.num
    and T2.date = M.date

or if you want the most recent date for all matching records in the table:

with maxdate as (
    select  max(T2.date) as date
      from  table2 T2
      join  table1 T1
        on  T2.num = t1.num
)
select  t2.num, t2.date, t2.amount
  from  table2 T2
  join  table1 T1
    on  T2.num = T1.num
  join  maxdate M
    on  T2.date = M.date

Try this query:

SELECT b.*
  FROM  Table1 a INNER JOIN
                (
        SELECT a.*, 
               ROW_NUMBER() OVER(PARTITION BY a.Num ORDER BY Date DESC) rnk
          FROM Table2  a
        ) b
        ON a.Num = b.Num
        AND rnk = 1

Try this

select t2.* from table2 as t2 inner join table1 as t1 on t2.num=t2.num
inner join
(
Select t2.num,max(t2.amount) as amount from table2 
group by  t2.num
) as t3 on t2.amount=t3.amount

DECLARE @t1 TABLE(num INT)
DECLARE @t2 TABLE(num INT, [date] NVARCHAR(5), amount INT)

INSERT INTO @t1 (num) VALUES (1),(2),(3)

INSERT INTO @t2 (num,[date],amount) VALUES (1,'12/31',30)
INSERT INTO @t2 (num,[date],amount) VALUES (1,'12/30',31)
INSERT INTO @t2 (num,[date],amount) VALUES (1,'12/29',20)
INSERT INTO @t2 (num,[date],amount) VALUES (2,'12/31',100)
INSERT INTO @t2 (num,[date],amount) VALUES (2,'12/30',90)
INSERT INTO @t2 (num,[date],amount) VALUES (3,'12/31',12)
INSERT INTO @t2 (num,[date],amount) VALUES (4,'11/01',1)

SELECT t2.num,t2.[date],amount 
FROM @t1 t1 JOIN @t2 t2 ON t1.num = t2.num
WHERE t2.[date] in (
                        SELECT MAX(t3.[date]) 
                        FROM @t2 t3 
                        WHERE t3.num = t2.num
                    )
ORDER BY t2.num