MySQL Orderby a number, Empty Strings (or 0's) Last

Question

Hi,

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 string 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 haven't been able to find a function which does what I am after, through google.

Many Thanks,

JonB

Answer 1

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

Answer 2

you can trying joining two sub queries where one select id > 0 and not empty and the other select empty and 0 only

Answer 3

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

Answer 4

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.