CODEWITHSUNDEEP
sqlsql-server
TheBoubou
TheBoubou

Reputation: 19933

Find the most recent record based on a specific value from the same table

I have a MyTable with this values (columns Id and MyDate)

10      2019-01-01
10      2018-01-01
25      2020-01-01
25      2005-01-01

I'd like keep record based on the most recent date, the result should be

10      2019-01-01
25      2020-01-01

Do you have an idea ?

Thanks,

Upvotes: 1

Views: 71

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Often, the faster method (with the right indexing) is:

select t.*
from t
where t.mydate = (select max(t2.mydate) as t t2 where t2.id = t.id);

The best index is on (id, mydate).

Note: For a small amount of data, the approach doesn't make much difference.

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try this using ROW_NUMBER (Transact-SQL) 

Create table MyTable (Id int, DtDate Date)
insert into MyTable Values
(10, '2019-01-01'),
(10, '2018-01-01'),
(25, '2020-01-01'),
(25, '2005-01-01')

select * from (
select id
    , dtDate
    , ROW_NUMBER() OVER(Partition By Id ORDER BY DtDate DESC) AS RowNo
from MyTable
)a where RowNo = 1

Live db<>fiddle demo.

Upvotes: 1

tletle
tletle

Reputation: 726

Try this:

 select Id , MyDate
    from (select m.*,
                 row_number() over (partition by Id order by MyDate desc) as rowNum
          from MyTable m
         ) t
    where rowNum = 1

Upvotes: 0

juergen d
juergen d

Reputation: 204904

select id, max(myDate)
from myTable
group by id

Upvotes: 0

Red Devil
Red Devil

Reputation: 2403

Try this:

select t.* from (
select *,row_number() over (partition by ID order by date desc) as RN from Table ) t
where rn=1

Upvotes: 0

Related Questions