Need a query

Question

myTable

id        name          date

1         A               2012-01-02

2         B               2010-05-07

3         C               2007-11-07

4         A               2011-08-04

5         C               2011-03-14

i need a query to return one row for each name that has maximum date

id       name      date

1        A             2012-01-02

2        B             2010-05-07

5        C             2011-03-14

Answer 1

Try the below:

;With cte
as
(Select *, Row_Number()Over(partition by name Order by date desc)Rn From tablename)
Select * From cte where rn=1

Answer 2

Askan,

this shud work:

--Method 1
select id,name,date 
from 
(
	select id,name,date,row_number() over(partition by name order by date desc) rnum
	from #temp
) tt
where tt.rnum=1

--Method 2
select t.*
from #temp t
inner join (
select name,max(date) date
from #temp
group by name) tt on t.name=tt.name and t.date=tt.date
order by t.id

--Method 3
select a.*
from #temp a
left join #temp b on a.date<b.date and a.name=b.name
where b.id is NULL 

---

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>