CODEWITHSUNDEEP
sqlsql-servert-sqldatedistinct
TheDataGuy
TheDataGuy

Reputation: 3118

T-SQL : return latest value of each ID

I have a table looks like

ID, name, Likes, Login_time

select * from mytbl

enter image description here

I want to filter this table:

distinct ID, name like, login_time(last login time)

enter image description here

I tried this query, but it didn't work.

select * 
from
    (select 
         name, likes, login_time
         rank() over (partition by id order by login_time desc) as rank
     from 
         mytbl) t
where 
    t.rank = 1

Upvotes: 1

Views: 704

Answers (2)

Ramesh Kharbuja
Ramesh Kharbuja

Reputation: 407

use row_number instead of rank

select * 
from
(
select 
        id, name, likes, login_time,
         ROW_NUMBER() over (partition by id order by login_time desc) as rank
     from 
         mytbl )t
where 
    t.rank = 1

Upvotes: 1

TriV
TriV

Reputation: 5148

Try this

 ;WITH temps AS
    (
   SELECT Id, name, likes, login_time, row_number() over(PARTITION BY Id ORDER BY login_time desc) AS RowIndex
  )
SELECT Id, name, likes, login_time FROM temps 
WHERE RowIndex = 1

Upvotes: 0

Related Questions