Select from SQL table by one table's highest values

Question

I have a table with userIDs, a table with testIDs, and a table that pairs userIDs with testIDs. How could I, using only SQL, select each userID once based on the highest testID it corresponds with?

userIDTable: userID 1, userID 2, userID 3

mediatorTable: userID 1 testID 1, userID 2 testID 2, userID 1 testID 3, userID 2 testID 7, userID 3 testID 5

testIDTable: testID 1, testID 2, testID 3, testID 5, testID 7

SELECT userID 1 testID 3, userID 2 testID 7, userID 3 testID 5

score 0 · Accepted Answer · answered Dec 26 '20 at 00:57

You want just one row per user, with the biggest testid.

One option is joins, and a correlated subquery to filter on the expected test.

select ... -- enumerate the columns that you want here
from mediator m 
inner join users u on u.userid  = m.userid
inner join tests t on t.test_id = m.testid
where m.testid = (
    select max(m1.test_id)
    from mediator m1
    where m1.userid = m.userid
)

In MySQL 8.0, you can also use window functions:

select ... 
from (
    select m.*, 
        row_number() over(partition by userid order by testid desc) rn 
    from mediator m 
) m
inner join users u on u.userid  = m.userid
inner join tests t on t.test_id = m.testid
where m.rn = 1

Select from SQL table by one table's highest values

1 Answers1