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

Question

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.

wpnpeiris · Accepted Answer · 2020-03-28 18:20:23Z

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.

Gordon Linoff · Accepted Answer · 2020-03-28 17:08:51Z

0

This query should work:

SELECT t1.ShopID AS ShopID_1, t2.ShopID AS ShopID_2, t1.ArticleID
FROM <yourtable> t1 JOIN
     <yourtable> t2
     ON t1.ArticleID = t2.ArticleID AND t1.Price > t2.Price;

That is, you need a self-join and appropriate table aliases.

answered Mar 28, 2020 at 17:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

wpnpeiris Over a year ago

this may not work if there are more than two Shops per Article.

Gordon Linoff Over a year ago

@Sithroo . . . This answers the question: "And I would like to select pairs of shop IDs for which the price of the same article is higher. " If there are multiple shops, the OP will get multiple rows for each article. I see nothing in the question that suggests that the OP wants anything else.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related