"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.

GMB · Accepted Answer · 2020-09-08T13:58:36.460

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

edited Sep 08 '20 at 13:58

answered Sep 08 '20 at 13:43

GMB

216,147
25
84
135

This works like a charm. I am using MySQL 8 thought to be honest the first option looks like it makes more sense to me. However, while this returns the data I'd like to actually be in the table, how would I combine it with presumably some UPDATE/DELETE statements to commit the data to the table? eg. I'd like user 1's row to be edited to show the lowest time, and user 2's to be deleted – deeBo Sep 08 '20 at 13:53
@DeeBo: see my updated answer for a solution on how to delete the rows. – GMB Sep 08 '20 at 14:03
Thank you very much. (This updating/deleting part is what made my question different from the "duplicate") – deeBo Sep 08 '20 at 14:08

score 0 · Answer 2 · answered Sep 08 '20 at 13:46

0

I think you just want to group by the cube size and get the minimum of the other columns. You can do this with:

select min(userId), cubeSize, min(solveTime) from t group by cubeSize

answered Sep 08 '20 at 13:46

The Impaler

45,731
9
39
76

Hi, this one doesn't actually quite work. I think the `min(userId)` part is incorrect, because it doesn't actually return the userId associated with the lowest `solveTime`, it just returns the lowest `userId` that has a record that cubeSize. – deeBo Sep 08 '20 at 13:48
Yep, that's what you wanted... or not? Your expected result includes `1, 8, 125.30` -- That's not a row in the sample data, but just values composed from multiple rows. – The Impaler Sep 08 '20 at 13:49
Oh I see what you mean! I'm sorry, had a brain-blank there. Yes. While this returns the data I'd like to actually be in the table, how would I combine it with presumably some `UPDATE`/`DELETE` statements to commit the data to the table? eg. I'd like user 1's row to be edited to show the lowest time, and user 2's to be deleted – deeBo Sep 08 '20 at 13:51

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

2 Answers2