CODEWITHSUNDEEP
phpmysqlsql-order-by
Ford
Ford

Reputation: 567

MySQL multiple order by (kind of nested)

A strange problem, in that it is working but i dont understand why. I am hoping to learn why this is working. I have searched the net and stackOverflow for an answer but can not find one (i came out with this code through trial and error...)

I have been trying to order by 3 columns.

I wanted the results from the first 2 columns mixed together but first and then all results ordered by the third column (a date)

This is the basic database table structure:

NOTE: HomePage and FeaturedProfile can both be 0 (so any results that are both 0 should be below (and are!))

This is the SQL code that works:

SELECT DISTINCT
HomePage, Profile, Name
FROM tProfiles
ORDER BY (HomePage = 0 AND FeaturedProfile = 0), e.DateModified DESC

it is the ORDER BY that i dont understand as my logic would say use a 1 and not a 0 + i am not to sure what the brackets are doing either as i would assume the below would have worked (like in a PHP SUM) and seems more logical to me.

ORDER BY (HomePage, FeaturedProfile), e.DateModified DESC

Hope this questions is allowed as it is bugging me as to why this is working and i would like to learn this for future reference....

Thanks in advance, and i will be delighted to up-mark anyone that can help me understand this!

Upvotes: 0

Views: 347

Answers (3)

John Ruddell
John Ruddell

Reputation: 25842

When you make a conditional statement there is a boolean result either true, false or null... So the true or false result is the same as integers.. 0 for false and 1 for true.. So if you for instance wanted to know the numbers where a column was equal to a certain value you could do SUM(val = 1) and anytime that expression is true it would return 1 if it was false it would return 0 and be ommitted from the sum.. The final result would be the SUM of true conditions giving a count. When you do ORDER BY and then a conditional it does the same thing.. Either order by 0 or 1 ... so when its true they will be grouped in the 1 category... When its false it'll be grouped in the 0 category. Hope that makes sense

So with that understanding 

ORDER BY (HomePage = 0 AND FeaturedProfile = 0), e.DateModified DESC

Would mean that when homepage and featured profile are both 0 then that would be ordered second because its a true statement.. And then when its false it would be 0 so it would be ordered first in the default ASC order... And the the date would be ordered in descending order

Upvotes: 2

spencer7593
spencer7593

Reputation: 108410

The first expression in your ORDER BY clause, i.e:

(e.HomePage = 0 AND e.FeaturedProfile = 0)

Is MySQL shorthand, it's equivalent to the ANSI-standard:

 CASE WHEN (e.HomePage = 0 AND e.FeaturedProfile = 0) THEN 1
      WHEN (e.HomePage IS NOT NULL AND e.FeaturedProfile IS NOT NULL) THEN 0
      ELSE NULL
 END

The ASC/DESC keyword is omitted following that expression, so it defaults to ASC (ascending sequence).

The net result is that rows will be returned in this order:

First: rows where the boolean expression returns 0 (FALSE) (that is, any row that has non-NULL values for both of the columns, and a non-zero value for either (or both) columns.)

Followed by: rows where the boolean expression returns 1 (TRUE) (i.e. any row that has zero values for both of the columns)

Followed by rows where either of the columns is NULL.

You are right that there are other ways to achieve an equivalent result. For example:

ORDER BY (e.HomePage AND e.FeaturedProfile), e.DateModified DESC

The first expression in the ORDER BY clause is evaluated as boolean expression. That's saying the same thing: if either column is NULL, the result is NULL. Else if either column is FALSE (has a zero value), the result is FALSE (0). Else the result will be TRUE (1). The result of that expression is sorted in ascending order.

If handling NULL values in that exact way isn't important, if what we're really interested in is getting rows that have HomePage or FeaturedProfile as non-zero:

ORDER BY (e.HomePage OR e.FeaturedProfile) DESC, e.DateModified DESC

This is slightly different. If, for example, HomePage is 1 and FeaturedProfile is NULL, the expression will evaluate to 1, and the row will be sorted first. (The original would return NULL in this case, and the row would be sorted last.)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This is your order by clause:

ORDER BY (HomePage = 0 AND FeaturedProfile = 0),
         e.DateModified DESC

It has two components. The first is the boolean expression (HomePage = 0 AND FeaturedProfile = 0), which could take on three values: true, false, and NULL. Remember that MySQL treats booleans as integers with true being 1 and false being 0.

So, when HomePage and FeaturedProfile are both 0 (false), then the expression evaluates to TRUE. And, it has a value of 1. If either or both are 1, then the expression evaluates to FALSE, with a value of 0. So, these will go first in sort order, because 0 is smaller than 1.

I believe the way your data is set up, this says that you have data in one of these fields.

The second component is the date, so within each group, the date is sorted descending.

Upvotes: 2

Related Questions