Select pairs of values based on condition in other column – PostgreSQL

I’ve been trying to solve an issue for the past couple of days, but couldn’t figure out what the solution would be…

I have a table as the following:

+--------+-----------+-------+
| ShopID | ArticleID | Price |  
+--------+-----------+-------+
|      1 |         3 |   150 | 
|      1 |         2 |    80 |  
|      3 |         3 |   100 |  
|      4 |         2 |    95 |  
+--------+-----------+-------+

And I woud like to select pairs of shop IDs for which the price of the same article is higher. F.e. this should look like:

+----------+----------+---------+
| ShopID_1 | ShopID_2 |ArticleID|
+----------+----------+---------+
|        4 |        1 |       2 |
|        1 |        3 |       3 |
+----------+----------+---------+

… showing that Article 2 ist more expensive in ShopID 4 than in ShopID 2. Etc

My code so far looks as following:

SELECT ShopID AS ShopID_1, ShopID AS ShopID_2, ArticleID FROM table
WHERE table.ArticleID=table.ArticleID and table.Price > table.Price

But it doesn’t give the result I am searching for.

Can anyone help me with this objective? Thank you very much.

Answer

The problem here is about calculating Top N items per Group.

Assuming you have the following data, in table sales.

# select * from sales;
 shopid | articleid | price 
--------+-----------+-------
      1 |         2 |    80
      3 |         3 |   100
      4 |         2 |    95
      1 |         3 |   150
      5 |         3 |    50

With the following query we can create a partition for each ArticleId

select 
  ArticleID, 
  ShopID, 
  Price, 
  row_number() over (partition by ArticleID order by Price desc) as Price_Rank from sales;

This will result:

 articleid | shopid | price | price_rank 
-----------+--------+-------+------------
         2 |      4 |    95 |          1
         2 |      1 |    80 |          2
         3 |      1 |   150 |          1
         3 |      3 |   100 |          2
         3 |      5 |    50 |          3

Then we simply select Top 2 items for each AritcleId:

select 
  ArticleID,  
  ShopID, 
  Price
from (
  select 
    ArticleID, 
    ShopID, 
    Price, 
    row_number() over (partition by ArticleID order by Price desc) as Price_Rank 
  from sales) sales_rank
where Price_Rank <= 2;

which will result:

 articleid | shopid | price 
-----------+--------+-------
         2 |      4 |    95
         2 |      1 |    80
         3 |      1 |   150
         3 |      3 |   100

Finally, we can use crosstab function to get the expected pivot view.

select * 
from crosstab(
  'select 
    ArticleID,  
    ShopID, 
    ShopID
  from (
    select 
      ArticleID, 
      ShopID, 
      Price, 
      row_number() over (partition by ArticleID order by Price desc) as Price_Rank 
    from sales) sales_rank
  where Price_Rank <= 2')
AS sales_top_2("ArticleID" INT, "ShopID_1" INT, "ShopID_2" INT);

And the result:

 ArticleID | ShopID_1 | ShopID_2 
-----------+----------+----------
         2 |        4 |        1
         3 |        1 |        3

Note: You may need to call CREATE EXTENSION tablefunc; in case if you get the error function crosstab(unknown) does not exist.