Retreive Certain Values Last (mySQL)

ORDER BY colname ASC, but make 0 retreive last

[abcorn]

I have a small mySQL query:

SELECT * FROM `users` ORDER BY colname ASC

So, if there are values in colname of 0, 0, 0, 2, 5, 6, 8, then that is the order in which they would be retrieved.

However, I need it to retrieve them in this order: 2, 5, 6, 8, 0, 0, 0. Is there any way to do this?

And also, I need it to retrieve it in one statement.

Can anyone help me please?

[Demaestro]

I can think of an ugly way.

At time of select add a statement that says if value = 0 then value = 10000000 (a number that will always be bigger then any true value).

Then you can sort normally, the trick is you have to convert that number back to a '0' at time of display.

So it would look something like

select IF(colname=0,10000000,colname) as colname from users ORDER BY colname ASC

Then at time of display you would say something like

if $colname = '10000000':
print 0
else:
print $colname


Keep in mind this is a little hacky, but it will work.

Good Luck and post back with more questions if you have them.

[LifeinAsia]

Another possible way- something like:
SELECT *,colname AS Sort
FROM `users`
WHERE colname>0
UNION
SELECT *,1000000 AS Sort
FROM `users`
WHERE colname=0
ORDER BY Sort

[rocknbil]

Is there any way to do this?

Welcome aboard abcorn! You're in luck, I stumbled on this a month or so ago and it's embarrassingly simple. Reason you're lucky, I was able to remember it (which is questionable at my age:)

Link [webmasterworld.com] with full working sample.

select * from `users` order by colname>0 desc, colname asc

Should float all the values > 0 to the top. At first it doesn't seem like it would work, but after you think about it, the facepalm comes. :-)

It also works on textual values [webmasterworld.com].

[abcorn]

select * from `users` order by colname>0 desc, colname asc

Should float all the values > 0 to the top. At first it doesn't seem like it would work, but after you think about it, the facepalm comes. :-)

Wow! I really wasn't expecting so many responses so quickly. I tried rocknbil's suggestion and it worked perfectly. Thanks so much for the help!

[Demaestro]

RnB

Nice! I have never used that logic before. I am adding it to my code base.

Thanks