MySQL Order by two different fields at same time

Question

I'm trying to display items from my database, here's an example

user | 123 | abc Jack | 0 | b John | 0 | c Doe | 1 | a

and how I want it displayed on site is something like this

user | 123 | abc Doe | 1 | a Jack | 0 | b John | 0 | c

I want it orderying by abc(abc=1) AND 123 (cat then dog then chicken)

Tips:

abc being either 0 or 1

123 being multiple things like: chicken, dog, cat etc..

The field isn't named 123 it's just an example and this is the query i'm using right now: SELECT * FROM users order by paid=1, animal='cat', animal='dog' It's bringing paid users up first but isn't ordering by animal

So what's not working about your `ORDER BY` clause at the moment? — Rowland Shaw, Sep 27 '16 at 12:21
Your explanation and data seem to be coming from different planets. Is `123` (a *horrible* name for a column) a number or a string? — Gordon Linoff, Sep 27 '16 at 12:23
The tips don't clarify anything for me - if column 123 contains values like chicken or cat or dog why not show it in your example and how can column abc contain 0 or 1 ? — P.Salmon, Sep 27 '16 at 12:28
@GordonLinoff The field isn't named 123 it's just an example and this is the query i'm using right now: `SELECT * FROM users order by paid=1 order by animal='cat', animal='dog'` It's bringing paid users up first but isn't ordering by animal — ohpls, Sep 27 '16 at 12:33
@P.Salmon this is just the way how it is in the database and how the I'm looking to do it, I know it's a little bit annoying — ohpls, Sep 27 '16 at 12:50
In that case you may need a case statement to force the ordering — P.Salmon, Sep 27 '16 at 12:56

score 1 · Answer 1 · answered Sep 27 '16 at 12:57

drop table users;
create table users (name varchar(5),animal varchar(10),paid int);
insert into users values
('abc','dog',1),
('def','dog',0),
('ghi','chicken',0),
('jkl','cat',0);

select * from users 
order by paid desc,
        case 
            when animal = 'cat' then 1
            when animal = 'dog' then 2
            when animal = 'chicken' then 3
        end

result

+------+---------+------+
| name | animal  | paid |
+------+---------+------+
| abc  | dog     |    1 |
| jkl  | cat     |    0 |
| def  | dog     |    0 |
| ghi  | chicken |    0 |
+------+---------+------+

score 0 · Answer 2 · answered Sep 27 '16 at 12:22

0

This is explained in the documentation:

SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;

For more complex ordering, see here:

http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

answered Sep 27 '16 at 12:22

Jay

19,649
38
121
184

score 0 · Answer 3 · answered Sep 27 '16 at 12:22

0

This should work :

ORDER BY abc, 123

You don't need to write ASC in your case since ASC is the default value.

answered Sep 27 '16 at 12:22

Alex

478
2
11

score 0 · Answer 4 · answered Sep 27 '16 at 12:22

You need to use ~ORDER BY 123 DESC and abc ASC`

replace test with your table name.

select t.user,t.123,t.abc from test t order by t.123 DESC,t.abc ASC

Demo SQL Fiddle

Output

user    123     abc
Doe     1        a
Jack    0        b
John    0        c

score 0 · Answer 5 · answered Sep 27 '16 at 13:08

0

select a.user,a.123,a.abc from test a order by a.123 DESC,a.abc ASC

answered Sep 27 '16 at 13:08

Daniyal

184
5
24

MySQL Order by two different fields at same time

5 Answers5