locked
Need a query RRS feed

  • 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

    Tuesday, February 25, 2014 10:18 AM

Answers

  • 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'>


    • Edited by Jayakumaur (JK) Tuesday, February 25, 2014 10:42 AM another method
    • Marked as answer by Ashkan209 Wednesday, February 26, 2014 7:14 AM
    Tuesday, February 25, 2014 10:39 AM

All replies

  • 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


    Tuesday, February 25, 2014 10:20 AM
  • 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'>


    • Edited by Jayakumaur (JK) Tuesday, February 25, 2014 10:42 AM another method
    • Marked as answer by Ashkan209 Wednesday, February 26, 2014 7:14 AM
    Tuesday, February 25, 2014 10:39 AM