SQL, multiple sorting order

Question

I would like to ask if i can achieve a query with one "order by" without using union:

first_order = column1 != column2;
seconder_order = column1 = column2;
third_order = column is null;

ex. select * from table_A order by first_order , seconder_order , third_order

But when i try this its not working.

What im trying to do is to display on my table with this list first_order then second_order and last the third_order. Im no expert on sql. Hope you can help me on this.. Thanks in advance!

Explain what's not working. You can definitely sort by multiple columns. — Robbert
– Robbert, Commented Nov 17, 2014 at 5:31
my example above returns the list with third_oreder thats column2 is null. — edmund
– edmund, Commented Nov 17, 2014 at 5:36

turutosiya · Accepted Answer · 2014-11-17 06:48:14Z

2

If you're using MySQL, try :

ORDER BY (CASE
    WHEN column1 != column2 THEN 0
    WHEN column1  = column2 THEN 1
    WHEN column is null     THEN 2
    ELSE 3
END) ASC;

edited Nov 17, 2014 at 6:48

answered Nov 17, 2014 at 5:31

turutosiya

1,04510 silver badges17 bronze badges

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

4 Comments

edmund Over a year ago

Thanks for the quick reply. But im trying to use your query but im getting an error . Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE) ASC LIMIT 0, 1000'

Jonathan Leffler Over a year ago

@edmund: I believe the correct syntax uses CASE … END rather than CASE … END CASE.

edmund Over a year ago

YES! @ jonathan your also correct! After searching how to use CASE in mysql i finally run this query without error and achieved what im trying to do. I just remove CASE on END CASE. Thank you so much turutosiya! This is amazing!

turutosiya Over a year ago

thanks @JonathanLeffler I've just correct the statement.

Collectives™ on Stack Overflow

SQL, multiple sorting order

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related