David542 David542 - 1 month ago 5
MySQL Question

Pre-ordering a GROUP BY statement

This is a follow-up to a previous question: GROUP BY ordering.

I have the following sql table and data: http://sqlfiddle.com/#!9/81c3b6/2/0.

The basic un-ordered SQL statement is:

SELECT territory_id, platform_type_id, store_url
FROM main_itemmaster
GROUP BY platform_type_id

I want to get a single entry for each
that is in the table, with a preference for
. That is, if an entry exists where
, I would like to grab that one in the

What would be the correct SQL statement to return the GROUPed by statement with a preference for the US item first?


In MySQL, the safest way to do this probably involves variables:

select im.*
from (select im.*,
             (@rn := if(@p = platform_type_id, @rn + 1,
                        if(@p := platform_type_id, 1, 1)
             ) as rn
      from main_itemmaster im cross join
           (select @rn := 0, @p := '') params
      order by platform_type_id, (territory_id = 'US') desc
     ) im
where rn = 1;

The does not involve using the MySQL (mis)feature that permits columns in the SELECT of an aggregation query that are not aggregated and not in the GROUP BY.

Here is a SQL Fiddle showing it working.


On the subject of the order of evaluation of variables. From the documentation:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

The above code does, technically, read the variable in the same statement, but it is also in the same expression. The semantics of if() (and case which I sometimes also use) guarantee the order of evaluation of the expressions.