SELECT territory_id, platform_type_id, store_url
GROUP BY platform_type_id
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
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
SETstatements, 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
case which I sometimes also use) guarantee the order of evaluation of the expressions.