MySQL query with complex ORDER BY

Question

I have the following query:

SELECT id, title, adcontent, adtargetURL, locationpage, 
locationarg, locationid, displayorder, visible, schedstart, 
schedstop, created, modified, createdby, modifiedby
FROM ads            
ORDER BY locationpage, locationarg, locationid, title

I need to order the fields the following way:

sort by locationpage with any fields with a value of 'all' first, then the rest in ascending order
then by locationarg with any NULL or empty string values first, then the rest in asc
then by locationid with any NULL or 0 values first, the rest in asc
and within those, sort by displayorder of '1' first, then NULL, then '2'
and lastly, by title in ASC if any of those manage to all be the same

What should my ORDER BY look like to achieve this?

Here's what I have so far: (updated from below)

ORDER BY locationpage='all', 
         locationpage ASC, 
         locationarg ASC, 
         locationid ASC, 
         displayorder='1', 
         ISNULL(displayorder), 
         displayorder='2', 
         title ASC

...but this isn't working!

Besides `locationpage`, the other sort orders are just ` ASC` afaik, or just `` for that matter. — Ja͢ck, Oct 05 '12 at 13:20
True! I'd somehow forgotten that null/empty values come first alphabetically. Now I have this: `ORDER BY locationpage='all', locationpage ASC, locationarg ASC, locationid ASC, displayorder='1', ISNULL(displayorder), displayorder='2', title ASC` — Michelle, Oct 05 '12 at 13:25
Your order by does not make any sense. Remember that if you order something (lets say: Unique ID column) there is no sense in adding more fields to order. — Romo, Oct 05 '12 at 13:40
Take a look at a similar question of mine: http://stackoverflow.com/questions/10946632/changing-nulls-position-in-sorting — Andrius Naruševičius, Oct 05 '12 at 14:00

Mortalus · Answer 1 · 2012-10-05T13:38:30.647

1

Your best choice would be to use a calculated field that will generate order id according to your rules.. I'll add an example in a moment..

select
case
  when locationpage = "all" then 10
  when ISNULL(locationarg)  then 20
  ....
  else 50 
end as OrderID,
id, 
title, 
....

FROM ads            

ORDER BY OrderID DSEC

edited Oct 05 '12 at 13:38

answered Oct 05 '12 at 13:30

Mortalus

10,574
11
67
117

Use multiplication of 10 in your order identifiers that will help in the future if you will need to sqeeze something between two values... – Mortalus Oct 05 '12 at 13:41

score 0 · Answer 2 · answered Oct 05 '12 at 13:35

Give this a bash:

select id, title, adcontent, adtargetURL, locationpage, locationarg, locationid, displayorder, visible, schedstart, schedstop, created, modified, createdby, modifiedby 
from ads
order by case when locationpage='all' then 0 else 1 end, 
         locationpage, 
         case when locationarg is null or locationarg='' then 0 else 1 end,
         locationarg ASC, 
         case when locationid is null OR locationid='0' then 0 else 1 end, 
         locationid, 
         case when displayorder =1 then 0 when displayorder is null then 1 when displayorder = 2 then 2 else 3 end,
         title;

MySQL query with complex ORDER BY

2 Answers2