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 fi

Solution 1:

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

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

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

CASEWHEN (e.HomePage =0AND e.FeaturedProfile =0) THEN1WHEN (e.HomePage ISNOTNULLAND e.FeaturedProfile ISNOTNULL) THEN0ELSENULLEND

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:

ORDERBY (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:

ORDERBY (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.)

Solution 2:

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.

Solution 3:

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