Welcome to WebmasterWorld Guest from 54.196.244.186

Forum Moderators: open

Message Too Old, No Replies

Retreive Certain Values Last (mySQL)

ORDER BY colname ASC, but make 0 retreive last

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

New User

5+ Year Member

joined:July 9, 2010
posts: 4
votes: 0


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?
5:27 pm on July 9, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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.
5:52 pm on July 9, 2010 (gmt 0)

Moderator from US 

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

joined:Dec 10, 2005
posts:5551
votes: 24


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
6:03 pm on July 9, 2010 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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].
6:29 pm on July 9, 2010 (gmt 0)

New User

5+ Year Member

joined:July 9, 2010
posts: 4
votes: 0


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!
6:52 pm on July 9, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


RnB

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

Thanks