0

I have this data

UserId   LoginTime     Machine
100       1.1.2022        A
100       1.2.2022        B
101       1.3.2022        C
101       1.4.2022        D
101       1.5.2022        E

and I need this output by sql query

UserId     LoginTime    Machine
100         1.2.2022       B
101         1.5.2022       E

The latest records grouping by UserId and including Machine info

I tried group by and max functions but I need Machine info and when I try group by, results doesn't show up as single, because machine info is different. Could you help me with this query?

Stu
  • 30,392
  • 6
  • 14
  • 33
Serdar YILMAZ
  • 9
  • 1
  • 5
  • 1
    Does [this](https://stackoverflow.com/questions/1793054/select-top-1-with-a-group-by) answer your question? In your case you would use `ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC)` – T N Mar 01 '22 at 15:07

1 Answers1

1

use row_number()

select b.* from (select a.*, row_number() over(partition by userid order by logintime desc) rn
from table_name a) b where b.rn=1
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63