GROUP BY self-defined?

earswithfeet

Hi,

i use the GROUP BY in a query, but i would like to get the output not sorted alphabetically and also not by what i am using the query for, but in a self defined pattern.
e.g.:
SELECT status,COUNT(*) FROM day GROUP BY status;

would give me s.th. like:

morning - 56
night - 100
noon - 12

but now i would like to get it sorted like this:

morning - 56
noon - 12
night - 100

is s.th. like this possible directly with the MySQL query?
I know how to do it with php, but if its already possible to do it with the MySQL query, then it would be way better 🙂

Thanks,

earswithfeet

tomhath

it can be done with an ORDER BY and a CASE:

SELECT status,COUNT(*) FROM day GROUP BY status
ORDER BY
CASE when 'morning' then 0
when 'noon' then 1
else 2
end

tomhath

Well, that code won't run, here's another try (warning - still untested):

ORDER BY
CASE when status = 'morning' then 0
when status = 'noon' ...

mattr

MySQL does not have the CASE construct. It does have IF though.

Lars_Berg

So you say. The documentation says something else.

http://www.mysql.com/doc/en/Control_flow_functions.html

mattr

Well I'll be. I apologise then! 🙁 I wonder why I don't see it used more often (I am a Sybase guy and use it in T-SQL)

earswithfeet

Thanks a lot guys,
i will give it a try now! 🙂