CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110143

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 
    inner join main_territorypricing p on p.item_id=i.id
    inner join main_territorypricingavail a on a.tp_id=p.id 
    where imdb_url = 'http://imdb.com/title/tt1576422/' 
group by platform_type_id

Which gives me the following:

territory_id    platform_type_id    store_url
US  Amazon  http://www.amazon.com/dp/B00EQIHJAG
PT  ITUNES  https://itunes.apple.com/pt/movie/id582142080

However, I want to do a GROUP BY to return the territory_id="US" first if that exists. How would I do that?

This is one way I tried which looks quite dirty but does work in the version of mysql I'm using:

select * from 
(select territory_id, platform_type_id, p.store_url from main_itemmaster m 
    inner join main_iteminstance i on m.id=i.master_id 
    inner join main_territorypricing p on p.item_id=i.id 
    inner join main_territorypricingavail a on a.tp_id=p.id 
 where imdb_url = 'http://imdb.com/title/tt1576422/'
 order by territory_id='us' desc
) x group by platform_type_id

Which gives:

territory_id    platform_type_id    store_url
US  Amazon  http://www.amazon.com/dp/B00EQIHJAG
US  ITUNES  https://itunes.apple.com/us/movie/id582142080

Which is the correct result set I'm looking to get.

Here is a link to a SQL fiddle. I condensed all the data into one table to focus on the GROUP BY statement: http://sqlfiddle.com/#!9/81c3b6/2/0

Upvotes: 0

Views: 75

Answers (1)

Matt
Matt

Reputation: 14341

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
    CROSS JOIN (SELECT @prevplatform:='',@PlatFormRowNum=0) var
  ORDER BY
    platform_type_id
    ,CASE WHEN territory_id = 'US' THEN 0 ELSE 1 END
    ,territory_id
) t
WHERE
  t.PlatformRowNum = 1
ORDER BY
  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?

Upvotes: 2

Related Questions