Xi Vix Xi Vix - 2 months ago 5
MySQL Question

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

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;

Answer

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.

Comments