MySQL sort by two columns

Question

I have a following table.

id    name     data
1     DAN      xxxxxxxxx
2     ANTONY   xxxxxxxxx
3     DAN      xxxxxxxxx
4     DAN      xxxxxxxxx
5     JOSEPH   xxxxxxxxx
6     ANTONY   xxxxxxxxx
7     JOSEPH   xxxxxxxxx

I want to first sort the table using ID and then group by name. eg.

7    JOSEPH  xxxxxxxxx
5    JOSEPH  xxxxxxxxx
6    ANTONY  xxxxxxxxx
2    ANTONY  xxxxxxxxx
4    DAN     xxxxxxxxx
3    DAN     xxxxxxxxx
1    DAN     xxxxxxxxx

I tried various combinations of ORDER BY and also tried ORDER BY FIELD but unable to get the desired result

have you tried select * from table order by id,name ?

moghya
– moghya

2018-03-14 09:38:12 +00:00
Commented Mar 14, 2018 at 9:38 — moghya
– moghya, Commented Mar 14, 2018 at 9:38
Yes I tried that, but didn't get the expected results

Antony
– Antony

2018-03-14 09:39:42 +00:00
Commented Mar 14, 2018 at 9:39 — Antony
– Antony, Commented Mar 14, 2018 at 9:39
ORDER BY correlated subquery?

jarlh
– jarlh

2018-03-14 09:41:16 +00:00
Commented Mar 14, 2018 at 9:41 — jarlh
– jarlh, Commented Mar 14, 2018 at 9:41

Radim Bača · Accepted Answer · 2018-03-14 09:42:04Z

2

Try using

select *, (select max(id) from tab t2 where t2.name = t1.name) m
from tab t1
order by m desc, id desc;

dbfiddle demo

answered Mar 14, 2018 at 9:42

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2018-03-14 09:57:34Z

0

You could also use window function to get the maximum id for each name and use them for ordering purpose.

SELECT *,
        MAX(id) OVER(PARTITION BY name ORDER BY id DESC) Ord
FROM table t
ORDER BY Ord DESC

edited Mar 14, 2018 at 9:57

answered Mar 14, 2018 at 9:49

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-03-14 10:55:42Z

0

You can use a subquery in the order by, so you can express this as:

select t.*
from t
order by (select max(id) from t t2 where t2.name = t.name) desc, id desc;

answered Mar 14, 2018 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL sort by two columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related