SQLTeam.com | Weblogs | Forums

How to select newest record between several rows with same data except date


im fairy new to SQL and im practicing with queries and whatnot, maybe someone here can help. i have a table that may have different rows with the same data except the date (example below)

Id Name LastName Date amount
2 John Doe 1-1-2016 100.00
10 John Doe 2-1-2016 100.00
100 John Doe 3-1-2016 100.00

and so on, the id jumps because there are other records for other people , i know i can run the select query base let say on the name but show me all the records for this person but how can i run it and only show me the most recent one. thank you


This ought to do it:

with cte
  as (select *       /* only select the fields you need */
            ,row_number() over(partition by [name]
                               order by [date] desc
                                       ,id desc
             as rn
        from yourtable
select *       /* only select the fields you need */
  from cte
 where rn=1