CODEWITHSUNDEEP
sqlpostgresql
Caspian Peavyhouse
Caspian Peavyhouse

Reputation: 63

SQL Filter otherwise duplicate entries based on a column

I have a table that looks like this:

╔════════════╦═════════╦═══════════╗
║ Machine_ID  ║ User_ ID ║ User_Role  ║
╠════════════╬═════════╬═══════════╣
║ Mach_001    ║ N00001   ║         3  ║
║ Mach_001    ║ N00001   ║         4  ║
║ Mach_001    ║ N00002   ║         3  ║
║ Mach_001    ║ N00002   ║         2  ║
║ Mach_002    ║ N00001   ║         3  ║
║ Mach_002    ║ N00002   ║         3  ║
╚════════════╩═════════╩═══════════╝

I'm trying to get the the rows with the highest User_Role for each Machine_ID - User_ID as such:

╔════════════╦═════════╦═══════════╗
║ Machine_ID  ║ User_ID  ║ User_Role  ║
╠════════════╬═════════╬═══════════╣
║ Mach_001    ║ N00001   ║ 4          ║
║ Mach_001    ║ N00002   ║ 3          ║
║ Mach_002    ║ N00001   ║ 3          ║
║ Mach_002    ║ N00002   ║ 3          ║
╚════════════╩═════════╩═══════════╝

How do I go about doing this? With my data, I could have many Machine_ID-User_ID duplicates with different Role levels, but I'd just like the highest role.

Edit: Working off of Gordon Linoff's answer, I was able to get what I was looking for:

SELECT Machine_ID, User_ID, User_Role
FROM (SELECT ex_rt.*,
        row_number() OVER (PARTITION BY Machine_ID, User_ID, ORDER BY User_Role DESC) AS seqnum
      FROM ex_rt
     ) sub
WHERE seqnum = 1;

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In most databases, you can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by machine_name, user_ID order by user_role desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions