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

1

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

2

This ought to do it:

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