sort data by specific order sequence (mysql)

Question

So, let say I have this data

id | value | group
1  | 100   | A
2  | 120   | A
3  | 150   | B
4  | 170   | B

I want to sort it so it become like this

id | value | group
1  | 100   | A
3  | 150   | B
2  | 120   | A
4  | 170   | B

there will be more group than that, so if I the data ordered the group like (A,C,B,D,B,C,A), it will become (A,B,C,D,A,B,C)

What is the logic behind this sort order? Letters in order, A - Z, one at a time, and then start again, until no letters are left? What makes you pick one A over another A? Remember that database rows have no order to them, so I guess that choice is based on the id? One last burning question: WHY? — KIKO Software
– KIKO Software, Commented May 3, 2019 at 18:04
To @KIKOSoftware 's question, why is 1 | 100 | A before 2 | 120 | A? Because of the id or because of the value? What if they have the same value? — AbraCadaver
– AbraCadaver, Commented May 3, 2019 at 18:09
the 'value' or the 'id' doesn't matter here. the data sorted by the 'group'. so if the 'group' unique data is (A,B,C,D), then it should return data in that order. — Hafizh
– Hafizh, Commented May 3, 2019 at 18:15

forpas · Accepted Answer · 2019-05-03 19:03:50Z

1

You can add a counter column to the table, which will be used to sort the table:

select t.id, t.value, t.`group`
from (
  select t.id, t.value, t.`group`,
  (select count(*) from tablename 
   where `group` = t.`group` and id < t.id) counter
  from tablename t
) t
order by t.counter, t.`group`

See the demo.
Results:

| id  | value | group |
| --- | ----- | ----- |
| 1   | 100   | A     |
| 3   | 150   | B     |
| 2   | 120   | A     |
| 4   | 170   | B     |

edited May 3, 2019 at 19:03

answered May 3, 2019 at 18:40

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakesh Jakhar · Accepted Answer · 2019-05-03 18:29:08Z

0

You can approach this as

SELECT *
FROM `tablename`
ORDER BY 
row_number() OVER (PARTITION BY `group` ORDER BY `group`), `group`

edited May 3, 2019 at 18:29

answered May 3, 2019 at 18:20

Rakesh Jakhar

6,4082 gold badges13 silver badges20 bronze badges

3 Comments

KIKO Software Over a year ago

Ah, is there an OVER in MySQL now?

Rakesh Jakhar Over a year ago

@KIKOSoftware screen shoot attached

KIKO Software Over a year ago

Yes, I found it in the manual.

Collectives™ on Stack Overflow

sort data by specific order sequence (mysql)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related