The Database Forum

**bluepajamas** · 09-21-2005, 04:58 PM

I have a column called displayOrder in a table of articles. The default value is 0. I need to display the records I select from the table according to the displayOrder, with the records containing the default value (0) last. So 1,2,3,4,5...0,0,0,0 etc.

When I do 'SELECT * FROM tblArticles ORDER BY displayOrder' it gives me the records with displayOrder=0 first. Is there a way to fix this with a different select statement?

Or maybe I could set a different default value? I tried setting the default to NULL, but that returned things in the same order. I don't know what else to set the default to in order to get records with the default displayOrder to always appear last.

Any suggestions?

**chrishirst** · 09-21-2005, 05:05 PM

ORDER BY displayorder DESC

W3Schools

**Anacrusis** · 09-21-2005, 05:07 PM

I would change the default value. Of couse those come first becuase 0 is always first.
Change the default value to some obscenely high number that you will never reach, like 999999

**bluepajamas** · 09-21-2005, 05:46 PM

Yeah, I wasn't expecting 0 to not come first, that's just what it had defaulted to and null didn't work either. But I guess setting the default really high will work.

I was thinking there might be something like: "SELECT * FROM tblArticles WHERE (NOT displayOrder=0 ORDER BY displayOrder) AND displayOrder=0" but your solution is probably simpler.

Thanks.

**chrishirst** · 09-21-2005, 06:00 PM

... ORDER BY displayorder WHERE displayorder > 0 ;

**Anacrusis** · 09-21-2005, 08:37 PM

That is not valid syntax. You cannot have a 'where' clause after the 'order by' statement.

**chrishirst** · 09-22-2005, 03:17 AM

Yep
Blame lack of sleep and a copy & paste c**k up

PHP Code:


			
... WHERE displayorder > 0 ORDER BY displayorder ;

**Anacrusis** · 09-22-2005, 09:22 AM

But that will remove the records that have a displayorder of 0, I don't think he wants to do that, he just wants them to show at the end of the results list. I don't think there's a gracefull way of doing that.

**danlefree** · 04:08 AM

There's always a way to do it, if you want to hack at the MySQL all night and day...

(You might try using a mathematical function in the ORDER BY clause to make zero greater than any other number on the scale and sort in an ascending fashion ... but I don't have all night or day to put that together)

... or not