Group By Ordering

I have the following query in mysql: select territory_id, platform_type_id, p.store_url from main_itemmaster m inner join main_iteminstance i on m.id=i.master_id inn

Solution 1:

So from the comments and the addition of the SqlFiddle it actually seems like you want to create a partitioned row number with a precedence on US per platform and then select the first record. One way of doing partitioned Row Numbers in mysql is to use variables here is an example:

SELECT
  territory_id
  ,platform_type_id
  ,store_url
FROM
( SELECT*
    ,@PlatFormRowNum:= IF(@prevplatform= platform_type_id, @PlatFormRowNum+1, 1) as PlatformRowNum
    ,@prevplatform:= platform_type_id
  FROM
    main_itemmaster m
    CROSSJOIN (SELECT@prevplatform:='',@PlatFormRowNum=0) var
  ORDERBY
    platform_type_id
    ,CASEWHEN territory_id ='US'THEN0ELSE1END
    ,territory_id
) t
WHERE
  t.PlatformRowNum =1ORDERBY
  t.platform_type_id

SQL Fiddle: http://sqlfiddle.com/#!9/81c3b6/12

Basically this partitions the row number by platform, orders US before any other territory and then selects the first row foreach platform. The one question/trick is how do you choose which to return when US is not available for the platform simply the ascending alphabetical order of the territory_id?