mysql: how to have different order by based on field result

Question

I have a table of domain, subdomain, path, action, type, user that I to have results sorted based on the action field.

type represents the type of record (2 = domain, 3 = subdomain, 4 = path).

for the results with action = a then order by action asc, path desc, subdomain desc, user desc;

and

for the results with action = b then order by action asc, subdomain asc, path asc, user desc;

I need all of the above to be in one select statement that selects based on domain, subdomain, path. The select would start like:

select action, user 
from table1 
where (domain = 'testdomain.com' and type = 2) 
or (domain = 'testdomain.com' and subdomain = 'sub1'and type = 3) 
or (domain = 'testdomain.com' and path = 'path1' and type = 4) 
and (user is null or user = 'smith') 
order by ...

Thanks in advance.

Update ... Drew reported this as a duplicate. There wasn't much for me to go on in the referenced question but I took the leap and here's the query. The query did not work (syntax error):

select action, type, user from filterList 
where (domain = 'testdomain.com' and type = 2) 
or (domain = 'testdomain.com' and subdomain = 'sub1' and type = 3) 
or (domain = 'testdomain.com' and path = 'path1' and type = 4) 
and (user is null or user = 'smith') 
order by `action` asc, 
CASE `action` 
WHEN 'a' THEN order by path desc, subdomain desc, user desc 
WHEN 'b' THEN order by subdomain asc, path asc, user desc;

Is this query going to return more than one row? For example, if it returns 5 rows, how do you know which `action` value to use? — Tyler Roper, Sep 27 '16 at 20:32
Yes, there will be multiple rows returned. There could be three action = a and two action = b in the results. — Xi Vix, Sep 27 '16 at 20:38

score 1 · Accepted Answer · answered Sep 27 '16 at 21:25

It's possible, but it will look weird... and you were on the right track:

order by `action` asc, 
CASE `action` WHEN 'a' THEN path ELSE NULL END DESC,
CASE `action` WHEN 'a' THEN subdomain ELSE NULL END DESC,
CASE `action` WHEN 'a' THEN user ELSE NULL END DESC,
CASE `action` WHEN 'b' THEN subdomain ELSE NULL END ASC,
CASE `action` WHEN 'b' THEN path ELSE NULL END ASC,
CASE `action` WHEN 'b' THEN user ELSE NULL END DESC

I am guessing the syntax error you got was because you can't put an ORDER BY clause in a CASE statement.

mysql: how to have different order by based on field result

1 Answers1