Sorting values lowest to highest except zero

[SixTimesEight]

I have a situation where I'm requesting data from MySQL and sorting it by an int(10) field from lowest to highest, but I need zeros to be the last results rather than the first.

I'd like to avoid simply excluding the zeros from the results.

Can anyone help?

[Habtom]

In the query, I am not sure if there is anything you can do.

Depending on how you display it, when you loop through the rows, you can collect the ones having the $row[thevalue] == 0 into a variable, and then attach them back again at the end.

I would have done something similar.

Ofcourse, there could be easier ways out of this.

[coopster]

A modified UNION may work. Here is a relative thread with some links that may offer direction ....
[webmasterworld.com...]

[borntobeweb]

Interesting thread, so one solution would be something like:

select * from table order by if(sortfield,0,1), sortfield

Not sure how fast that is on large tables.

[SixTimesEight]

This is what I ended up with and appears to be working fine:

SELECT foo FROM bar ORDER BY foo = 0, foo ASC

Zeros are always the last results and the remaining results are sorted with ASC and DESC as they normally would be.

Left to right reading logic tells me that any result which equals 0 would be first, but that's not the case. To get zeros first you would put

foo = 0

last in the

ORDER BY

criteria.

I couldn't immediately find the section of the mysql docs about using multiple criteria in the

ORDER BY

statement, but if someone else knows where it is I'd appreciate a link. This is working for me but I don't exactly understand how or why.

Thanks for the input.

[edited by: SixTimesEight at 1:41 pm (utc) on Sep. 8, 2007]

[coopster]

any result which equals 0 would be first, but that's not the case.

Yes, actually it is the case. You are just misunderstanding what the result should be ... explanation coming in a moment, first ...

To get zeros first you would put foo = 0 last in the ORDER BY criteria

Not necessary, just leave it out entirely and just ORDER BY foo and the zeros will come first by natural order of integers -- you must have been thinking too hard on this one ;)

I couldn't immediately find the section of the mysql docs about using multiple criteria in the ORDER BY statement, but if someone else knows where it is I'd appreciate a link.

The ORDER BY definition is in the SELECT [dev.mysql.com] statement syntax and can have values of

[ORDER BY {col_name ¦ expr ¦ position} [ASC ¦ DESC], ...]

where expr is an expression. Note that the use of

position

is deprecated because the syntax has been removed from the SQL standard.

Now for an explanation as mentioned earlier. You are misunderstanding your sort order definition. The definition "n1 = 0" is an expression which is going to return true or false when it compares the value of n1 for each row to the constant value of zero. The reason for your sort order is because your expression is returning TRUE for only those rows where foo = 0. Example, for clarification ...

CREATE TABLE mynumbers (n1 INTEGER UNSIGNED); 
INSERT INTO mynumbers (n1) VALUES(4),(2),(0),(3),(1); 
SELECT n1 FROM mynumbers ORDER BY n1 = 0, n1; 
+----+ 
¦ n1 ¦ 
+----+ 
¦  1 ¦ 
¦  2 ¦ 
¦  3 ¦ 
¦  4 ¦ 
¦  0 ¦ 
+----+

Yep, just like you said, and just as it should, but not what you expect! Now, show what n1 = 0 would look like ...

SELECT n1 = 0 AS nn, n1 FROM mynumbers ORDER BY n1 = 0, n1; 
+----+----+ 
¦ nn ¦ n1 ¦ 
+----+----+ 
¦  0 ¦  1 ¦ 
¦  0 ¦  2 ¦ 
¦  0 ¦  3 ¦ 
¦  0 ¦  4 ¦ 
¦  1 ¦  0 ¦ 
+----+----+

See how it is returning a boolean true/false value? In the first row the expression n1 = 0 is false because n1 in this row happens to be 1, so 0 (zero) is returned. The second row has an expression of false too, because n1 is 2 in this row. Again, zero is returned, and so on until it gets a TRUE value. The first column is sorted in order, false (or zeros) first, followed by true (or 1's) and then the second part of your ORDER BY expression is applied which is the actual value of n1.

A UNION is going to be fastest, by the way.

(SELECT 1 AS sort_col, n1 FROM myNumbers WHERE n1 > 0 ORDER BY n1) 
UNION ALL 
(SELECT 2 AS sort_col, n1 FROM myNumbers WHERE n1 = 0 ORDER BY n1) 
ORDER BY sort_col, n1;

A trick is to add your own "sort column" and sort the UNION query accordingly.