What happens when ORDER BY is given a number?
I just started learning MySQL, and the book I'm reading has the following example:
SELECT last_name, first_name, birth FROM president
ORDER BY IF(birth IS NU开发者_运维知识库LL,0,1), birth DESC;
What I wish to know is how those numbers (0 and 1) affect the behavior of ORDER BY
--when you give ORDER BY
a number, what does it do with it?
A hint I found is that random ordering is done the following way:
SELECT * from TABLE ORDER BY rand();
But I still don't know why it works.
It orders the data so that the rows where IF(birth IS NULL, 0, 1)
returns 0 appear before the rows where it returns 1.
You might care to notice that this is quite different from what happens if you write:
ORDER BY 1;
In this case, it orders by the first column in the result list. This is obsolescent syntax, but still required by the SQL standard.
精彩评论