homepage Welcome to WebmasterWorld Guest from 54.196.168.78
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Sorting values lowest to highest except zero
SixTimesEight




msg:3442552
 1:08 am on Sep 6, 2007 (gmt 0)

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




msg:3442728
 6:06 am on Sep 6, 2007 (gmt 0)

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




msg:3444771
 5:08 am on Sep 8, 2007 (gmt 0)

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

borntobeweb




msg:3444780
 5:46 am on Sep 8, 2007 (gmt 0)

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




msg:3444971
 1:40 pm on Sep 8, 2007 (gmt 0)

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




msg:3446520
 5:34 pm on Sep 10, 2007 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
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