MySQL Orderby a number, Empty Strings (or 0's) Last
Just asked a question pretty similar to this one...
Currently I am doing a very basic OrderBy in my statement.
SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC
The problem with this is that empty strin开发者_C百科g entries for 'position' are treated as 0. Therefore all entries with position as empty string appear before those with 1,2,3,4. eg:
'', '', '', 1, 2, 3, 4
or:
0, 0, 0, 1, 2, 3, 4
Is there a way to achieve the following ordering:
1, 2, 3, 4, '', '', ''.
or:
1, 2, 3, 4, 0, 0, 0.
I assume the solution may have some kind of replace function but I am not able to find a function which does what I am after.
SELECT *
FROM tablename
WHERE visible=1
ORDER BY
case when position in('', '0') then 1 else 0 end,
position ASC,
id DESC
You might try a CASE statement, like this:
SELECT *
FROM tablename
WHERE visible = 1
ORDER BY CASE position WHEN '' THEN '9999' ELSE position END CASE ASC,
ID DESC
You say that position
contains empty string entries...Do you really mean empty string, or do you mean NULL
? If it actually contains NULL
entries, you should use a slight modification of Orbman's statement:
SELECT *
FROM tablename
WHERE visible=1
ORDER BY
COALESCE(position, ~0)
, id DESC
COALESCE()
returns the value of the first argument that is NOT NULL
. The ~0 is a piece of black magic that will get you the maximum integer value supported by MySQL. (~ does a bitwise negation, turning all 0 bits to 1). So, in this case, if position IS NULL
is true, it will return 18446744073709551615
, else it will return the value of position
.
I'd also like to point out that the data type of your position
column should most likely be of some integer type (see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html). Because you mention empty strings, I think you should check your table definition by doing SHOW CREATE TABLE <tablename>
. If position is not an integer type, I would advise you to change it. The main reason is that strings, even if they look like numbers, don't sort as numbers.
you can trying joining two sub queries where one select id > 0 and not empty and the other select empty and 0 only
精彩评论