CODEWITHSUNDEEP
mysqlsqlsql-order-by
Nebu
Nebu

Reputation: 1793

Sort a table but keep groups of rows together

How do I sort a table by it's minimum value per group but at the same time keep a group of rows together. Below a simple example of what i am trying to accomplish. The table is sorted by the lowest group value, but the group remains together. I am pretty sure this question has been asked already but i could not find an answer.

+---------+-------+
|  Group  | value |
+---------+-------+
|    1    |  3.99 |
|    1    | 10.99 |
|    3    | 12.69 |
|    1    | 20.95 |
|    2    | 19.95 |
|    3    | 10.09 |
+---------+-------+

Desired output

+---------+-------+
|  Group  | value |
+---------+-------+
|    1    |  3.99 |
|    1    | 10.99 |
|    1    | 20.95 |
|    3    | 10.69 |
|    3    | 12.09 |
|    2    | 19.95 |
+---------+-------+

Upvotes: 3

Views: 633

Answers (2)

Sandeep Kashyap
Sandeep Kashyap

Reputation: 1

SELECT *,RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE,ID) FROM TEMP

Upvotes: 0

GMB
GMB

Reputation: 222582

If you are running MySQL 8.0, you can sort with window functions:

select t.*
from mytable t
order by min(value) over(partition by grp), value

In earlier versions, one option is to join an aggregate subquery:

select t.*
from mytable t
inner join (
    select grp, min(value) min_value from mytable group by grp
) m on m.grp = t.grp
order by m.min_value, t.value

Upvotes: 4

Related Questions