CODEWITHSUNDEEP
sqlmariadb
Wales
Wales

Reputation: 23

Mysql order result with subgroups order

As example I have table

id param1 param2
1 c 1
2 a 2
3 d 3
4 a 1
5 b 3
6 a 3
7 e 4

pair param1&param2 is unique

I want to get this result where any items with same param1 grouped by id in own subgroup

id param1 param2
1 c 1
2 a 2
4 a 1
6 a 3
3 d 3
5 b 3
7 e 4

Any suggestions how to do this?

Upvotes: 0

Views: 53

Answers (1)

MatBailie
MatBailie

Reputation: 86706

On the assumption that "groups" are ordered according to the MIN(ID) in the "group", and that rows within a "group" are ordered according to the id...

WITH
  summarised AS
(
  SELECT
    *,
    MIN(id) OVER (PARTITION BY param1) AS group_min_id
  FROM
    YourTable
) 
SELECT
  *
FROM
  summarised
ORDER BY
  group_min_id,
  id

Using correlated sub-query in place of window function...

SELECT
  *,
  (SELECT MIN(id) FROM yourTable AS lookup WHERE lookup.param1 = yourTable.param1) AS group_min_id
FROM
  yourTable
ORDER BY
  group_min_id,
  id

Upvotes: 1

Related Questions