| Retreive Certain Values Last (mySQL)
ORDER BY colname ASC, but make 0 retreive last |
abcorn
msg:4167281 | 5:16 pm on Jul 9, 2010 (gmt 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?
|
Demaestro
msg:4167286 | 5:27 pm on Jul 9, 2010 (gmt 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.
|
LifeinAsia
msg:4167303 | 5:52 pm on Jul 9, 2010 (gmt 0) |
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
msg:4167309 | 6:03 pm on Jul 9, 2010 (gmt 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].
|
abcorn
msg:4167319 | 6:29 pm on Jul 9, 2010 (gmt 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!
|
Demaestro
msg:4167336 | 6:52 pm on Jul 9, 2010 (gmt 0) |
RnB
Nice! I have never used that logic before. I am adding it to my code base.
Thanks
|
|
|
