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 arrange the fields as follows:

• sort by link with any fields with the value "all", and the rest in ascending order
• then using locationarg with any null or empty string and then the rest in asc
• then through locationid with any null or 0 values, the rest in asc
• and inside them sort by indicator '1' first, then NULL, then '2'
• and finally, under the heading in ASC, if any of them can be the same

What should my ORDER BY look like?

Here is what I still have: (updated below)

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

... but it does not work!

Answer 1

Your best bet is to use a calculated field that will generate an order ID in accordance with your rules. I will 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 

Answer 2

Give this 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;