1

So let's say I have a table that looks something like this

Name Department Login
Batman A1 03-01-2022
Spiderman A1 03-04-2022
Spiderman B3 03-02-2022
Superman B3 03-08-2022
Catwoman A2 03-23-2022
Venom C2 03-25-2022
Batman A1 03-12-2022

Now, I would like to get the latest Login value for each unique name (in reality this is a unique ID but in this example I put names just for readability). The problem is that if a Name has logged in from two (or more) different departments, I get two results (or more) for that name, but I would only want the latest Login regardless of department ASWELL as the Name and the Department from where the last login took place.

I've tried multiple variations of queries and the closest I've gotten is this:¨

SELECT Name, Department, max(Login)
FROM table
GROUP BY Name, Department

Which won't work for Spiderman because he has logged in from both A1 and B3.

Any tips on how to accomplish what I want?

Actual output:

Name Department Login
Spiderman A1 03-04-2022
Spiderman B3 03-02-2022
Superman B3 03-08-2022
Catwoman A2 03-23-2022
Venom C2 03-25-2022
Batman A1 03-12-2022

Expected output:

Name Department Login
Spiderman A1 03-04-2022
Superman B3 03-08-2022
Catwoman A2 03-23-2022
Venom C2 03-25-2022
Batman A1 03-12-2022
kela
  • 11
  • 2
  • Does this answer your question? [Get top n records for each group](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Stu Mar 29 '22 at 16:09
  • If you extract name and department you always will get 2 results (in this case) so reconsider what you need. Doing the select on name and max(login) gives you unique result but with department will be always two. – Evgeni Enchev Mar 29 '22 at 16:13
  • Sample data is great, but you also need to _specify_ the expected result. – jarlh Mar 29 '22 at 16:18
  • 1
    Yeah that's pretty much my problem. If I only needed the name it would be no big deal but I need to know aswell from what department their last login was and I can't seem to find any easy way around this @jarlh Sorry about that, I updated the post with further clarification – kela Mar 29 '22 at 16:19
  • But you have it! If you want only the last department just add ROWNUM < 2 or TOP 1 or something like this to your sentence. Up to your database – Evgeni Enchev Mar 29 '22 at 16:21
  • you can use analytic function for this ROW_NUMBER() – DB08 Mar 29 '22 at 16:22
  • Alright thanks guys, I think I will be able to get it now when I get into work tomorrow. Thanks a bunch!! – kela Mar 29 '22 at 16:26
  • Which dbms are you using? – jarlh Mar 29 '22 at 16:38

2 Answers2

1
SELECT Name, Department, Login
FROM (
    SELECT Name, Department, Login
        ,row_number() over (partition by Name, order by name, login desc) rn
    FROM [table]
) t
WHERE rn = 1
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

This should work :

select * from 
(SELECT Name, Department,login, 
row_number() over (partition by name order by login desc) rn
FROM table
)t
where t.rn = 1;
DB08
  • 151
  • 6