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 <field> ASC afaik, or just <field> for that matter. — Ja͢ck
– Ja͢ck, Commented Oct 5, 2012 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
– Michelle, Commented Oct 5, 2012 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
– Romo, Commented Oct 5, 2012 at 13:40

Mortalus · Accepted Answer · 2012-10-05 13:38:30Z

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 5, 2012 at 13:38

answered Oct 5, 2012 at 13:30

Mortalus

10.7k12 gold badges75 silver badges122 bronze badges

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

1 Comment

Mortalus Over a year ago

Use multiplication of 10 in your order identifiers that will help in the future if you will need to sqeeze something between two values...

Tom Mac · Accepted Answer · 2012-10-05 13:35:21Z

0

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;

answered Oct 5, 2012 at 13:35

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Collectives™ on Stack Overflow

MySQL query with complex ORDER BY

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related