"Merging" two rows by taking the lowest column value in MySQL

Question

I have a MySQL table similar to this:

userId | cubeSize | solveTime
-----------------------------
1      | 5        | 24.34
2      | 5        | 35.6
1      | 8        | 130.04
2      | 8        | 125.30
3      | 15       | 405.23

The composite primary key of the table is (userId, cubeSize).

Sometimes, I'd like to "merge" two users. Let's say we're merging user 2 into user 1. What I want to do is take the lowest solveTime of users 1 and 2 for each cube size and delete user 2's rows. With the result being:

userId | cubeSize | solveTime
-----------------------------
1      | 5        | 24.34
1      | 8        | 125.30
3      | 15       | 405.23

Does anyone have any ideas on how I could achieve this? I imagine it can probably be done in one or two queries using GROUP BY, but I'm not confident enough with SQL to be sure.

Answer 1

If you want the row with the smallest solveTime per cubeSize, then one option filters with a subquery:

select t.*
from mytable t
where solveTime = (select min(t1.solveTime) from mytable t1 where t1.cubeSize = t.cubeSize)

Alternatively, if you are running MySQL 8.0, you can use window functions:

select *
from (
    select t.*, rank() over(partition by cubeSize order by solveTime) rn
    from mytable t
) t
where rn = 1

If you wanted to delete the "other rows", then:

delete t
from mytable t
inner join (
    select cubeSize, min(solveTime) minSolveTime
    from mytable
    group by cubeSize
) t1 
on t1.cubeSize = t.cubeSize and t1.minSolveTime < t.solveTime