CODEWITHSUNDEEP
sqlpostgresql
Samuel Donadelli
Samuel Donadelli

Reputation: 347

Top 3 for each item in a new table - postgres

I have the table 1 below. For each product I want the 3 top supermarkets considering price in descent order. The wanted result is represented on table 2 below. I tried some SQL queries but none of them solved my issue. Thanks,

Table 1

    Product                        |Supermarket                        |Price
-----------------------------------+-----------------------------------+-----------------
    Bean                           |Adonis                             |87
    Coca-Cola                      |Adonis                             |24
    Butter                         |Adonis                             |60
    Bean                           |Emynia                             |43
    Coca-Cola                      |Emynia                             |84
    Butter                         |Emynia                             |50
    Bean                           |Chausia                            |43
    Coca-Cola                      |Chausia                            |61
    Butter                         |Chausia                            |42
    Bean                           |Ranucia                            |4
    Coca-Cola                      |Ranucia                            |23
    Butter                         |Ranucia                            |74
    Bean                           |Rodunia                            |66
    Coca-Cola                      |Rodunia                            |20
    Butter                         |Rodunia                            |26
    Bean                           |Serisia                            |2
    Coca-Cola                      |Serisia                            |35
    Butter                         |Serisia                            |16

Table 2

  Product                           |Supermarket                            |Price
------------------------------------+---------------------------------------+---------
    Bean                            |Adonis                                 |87
    Bean                            |Rodunia                                |66
    Bean                            |Emynia                                 |43
    Butter                          |Ranucia                                |74
    Butter                          |Adonis                                 |60
    Butter                          |Emynia                                 |50
    Coca-Cola                       |Emynia                                 |84
    Coca-Cola                       |Chausia                                |61
    Coca-Cola                       |Serisia                                |35

Upvotes: 1

Views: 77

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT "Product", "Supermarket", "Price"
FROM (
  SELECT "Product", "Supermarket", "Price", rank() OVER (
      PARTITION BY "Product" ORDER BY "Price" DESC
      ) AS rank
  FROM Table1
  ) t
WHERE rank <= 3

sqlfiddle demo

Since you have two Products with the same price in the top 3 for Bean, this will give you 4 results for that product:

PRODUCT SUPERMARKET     PRICE
Bean        Adonis       87
Bean        Rodunia      66
Bean        Chausia      43
Bean        Emynia       43
Butter      Ranucia      74
Butter      Adonis       60
Butter      Emynia       50
Coca-Cola   Emynia       84
Coca-Cola   Chausia      61
Coca-Cola   Serisia      35

Docs for more information on rank()

Upvotes: 2

Related Questions