homepage Welcome to WebmasterWorld Guest from 54.166.65.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved