SQLTeam.com | Weblogs | Forums

Only latest record


#1

Hi,

I have several data as below

ID Name Score

  1. John              50
    
  2. John              100
    
  3. John              80
    
  4. Birgit             100
    
  5. Birgit              90
    
  6. Tony 90
  7. Tony 100

I want to make resulting only the latest record, such as

No Name Score
3. John 80
2. Birgit 90
2. Tony 100

Please Help...

thanks


#2
; with cte as
(
select *, rn = row_number() over (partition by Name order by ID desc)
from  yourtable
)
select *
from cte
where rn = 1

#3

select *
from yourtable a
where id = (select max(id) from yourtable b where a.name = b.name)


#4

thanks a lot