Welcome to WebmasterWorld Guest from 54.144.79.200

Forum Moderators: open

Message Too Old, No Replies

Retreive Certain Values Last (mySQL)

ORDER BY colname ASC, but make 0 retreive last

     

abcorn

5:16 pm on Jul 9, 2010 (gmt 0)

5+ Year Member



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

5:27 pm on Jul 9, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

5:52 pm on Jul 9, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

6:03 pm on Jul 9, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

6:29 pm on Jul 9, 2010 (gmt 0)

5+ Year Member



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

6:52 pm on Jul 9, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



RnB

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

Thanks