Php/MySQL Multiple Order By

Set multiple order by in mysql query

You can use conditional ordering :

ORDER BY t.country = 'China', // 1 if true, 0 if false, ordered ASC 
         t.id DESC

MySQL accepts boolean expressions , so t.country = 'China' will be 1 if true, and 0 if false.

Mysql order by multiple columns with conditions

The way you do this in (My)SQL is, you provide a virtual field as ORDER BY condition.
Something like this should probably do the trick:

      SELECT a.id
           , a.date
           , a.package_id
           , a.package_expires_at
           , IF( a.package_expires_at < CURRENT_DATE,  1,         0                      ) AS is_expired
           , IF( a.package_expires_at < CURRENT_DATE,  a.`date`,  a.`package_expires_at` ) AS sort_date
           , p.`order`                                                                     AS sort_package_order
        FROM ads       AS  a
  INNER JOIN packages  AS  p 
          ON p.id = a.package_id
    ORDER BY IF( a.package_expires_at < CURRENT_DATE,  1,         0                      ) ASC
           , IF( a.package_expires_at < CURRENT_DATE,  1,         p.`order`              ) ASC
           , IF( a.package_expires_at < CURRENT_DATE,  a.`date`,  a.`package_expires_at` ) DESC

MySQL multiple order by (kind of nested)

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

Order a MySQL table by two columns

Default sorting is ascending, you need to add the keyword DESC to both your orders:

ORDER BY article_rating DESC, article_time DESC

PHP MySQL Order by Two Columns and limit by 1 result per first column

Try this
select name from pupils where age in (select max(age) from pupils group by sex);