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

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:

Expected output:

CodePudding user response：

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

CodePudding user response：

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;