Group by with 2 distinct columns in SQL Server

I have data like below

ID  Stat    Date
1   1       2009-06-01
2   1       2009-06-20
3   1       2009-06-10
4   2       2009-06-10

O/P to be like this..

ID     Stat   CDate
2      1      2009-06-20
4      2      2009-06-10

I have tried with below query and was unsuccessful, please suggest.

Select Distinct stat,MAX(Cdate) dt,id  From testtable
Gr开发者_StackOverflowoup By stat,id

Got the solution..

Select f1.id,f1.stat,f1.cdate From testtableas F1 Join(Select stat,MAX(cdate) as dt from testtable group by stat) as F2 On f2.stat=F1.stat and f2.dt=f1.cdate

SELECT t1.id, t1.stat, t1.date
FROM testtable t1
JOIN (SELECT stat, MAX(date) date FROM testtable GROUP BY stat) t2 ON t1.stat = t2.stat AND t1.date = t2.date
GROUP BY stat

I'm assuming you want the stat belonging to the maximum date, right?

select t1.id, t1.stat, t1.cdate 
from testtable t1,
  (select stat, max(cdate) max_date from testtable
   group by stat) t2
where t1.stat = t2.stat and t1.cdate = t2.max_date

You cannot add the id here. Because grouping on id will result will not be the desired result. id is distinct in its nature by default. so grouping on id will result all the data.

;with CTE AS
(
    Select stat,MAX(Cdate)Over(Partition by stat) as dt,id
    From testtable  
)
Select ID,stat,dt
From CTE
Inner JOIn testtable On testtable.id=CTE.ID and testtable.date=CTE.dt
Group By stat

I liked the solution by nicktrs, though. If you are using SQL SERVER 2005 or later, this might work for you;

select k.id, k.stat, k.cdate from(
    select id, stat, cdate, row_num=rownumber()
    over (partition by stat order by cdate desc) as k from testtab )
where k.row_num=1;

output of inner query goes like this:

ID  Stat    Date         Row_num
2   1       2009-06-20   1
3   1       2009-06-10   2
1   1       2009-06-01   3
4   2       2009-06-10   1

Output after full query is executed:

ID  Stat    Date
2   1       2009-06-20
4   2       2009-06-10

Hope this helps. Adieu.