Postgres query - get all records of lowest price per ID

Question

I have items table where I store information about items and their prices. It looks like this:

I want to select all item rows with the lowest price per item_code. It means the query should return ONE row per item_code in my table, which contains the lowest price. I'm using PostgreSQL.

Not sure where to start. Example DB data:

id | title |  item_code | price | site_id | store_id
1 | Shampoo | TEST1 | 10 | 1 | 1
2 | Shampoo | TEST1 | 5 | 2 | 1
3 | Shampoo | TEST1 | 12 | 2 | 1

What output do you desire if there are several items with the same item code that sport the minimum price for this code ? — collapsar, May 02 '22 at 16:48
"I want to select all item rows with the lowest price per item_code. It means the query should return ONE row per item_code in my table," Those are two different things. Which do you really want, all or one? — jjanes, May 02 '22 at 16:53

score 1 · Answer 1 · answered May 02 '22 at 16:38

1

Use DISTINCT ON:

SELECT DISTINCT ON (item_code) *
FROM items
ORDER BY item_code, price;

See the demo.

answered May 02 '22 at 16:38

forpas

160,666
10
38
76

This one fails if there are multiple items with the same item_code having the minimum price. – collapsar May 02 '22 at 16:44
@collapsar did you read: "the query should return ONE row per item_code in my table"? – forpas May 02 '22 at 16:45
I did, and it does not make sense if the complete item information is queried - what is the point in a random selection among the min price items ? – collapsar May 02 '22 at 16:47
@collapsar this is not my requirement. If you are unsure about it you can ask the OP. My understanding is that when the OP mentions *ONE row* this means *ONE row*. – forpas May 02 '22 at 16:49
Well, would you stand by your view if the OP asked for a query of all items with item_code XY, requiring the result set to have ONE row ? Anyway, I asked the OP. – collapsar May 02 '22 at 16:52
@collapsar my view is based on what I read in the question. When something is not clear I ask for clarifications, so well done for asking. – forpas May 02 '22 at 16:55
@collapsar I think forpas' solution is correct because the question states "*the query should return ONE row per item_code*" which I also understand as "each item_code only once" – May 02 '22 at 17:38
That's true. I don't care if there are multiple `item_code` rows with the minimum price, I need just one, no matter which one. – TheUnreal May 03 '22 at 10:34

collapsar · Answer 2 · 2022-05-02T16:38:24.063

0

Group your result set and use the MIN aggregate function:

    SELECT item_code
         , MIN(price)  min_price
      FROM items
  GROUP BY item_code
         ;

Join the result of this query with the original table if you need the the complete item record:

SELECT it.*
  FROM items it
  JOIN (
             SELECT item_code
                  , MIN(price)  min_price
               FROM items
           GROUP BY item_code
       ) gi ON ( gi.item_code = it.item_code )
 WHERE it.price = gi.min_price
     ;

See a live demo here on dbfiddle.co.uk

edited May 02 '22 at 16:38

answered May 02 '22 at 16:25

collapsar

17,010
4
35
61

Thanks, but I need to select the full row, not just the item_code - can you share how it can be adjusted? I tried it but it says that i must place all my fields in the GROUP by statement – TheUnreal May 02 '22 at 16:29

score 0 · Answer 3 · answered May 02 '22 at 17:05

You can also use ROW_NUMBER().

SELECT a.id, 
       a.title, 
       a.item_code, 
       a.price, 
       a.site_id, 
       a.store_id 
FROM 
(
    SELECT *, row_number() over(partition by item_code order by price) rn 
    FROM items
 ) a WHERE a.rn=1;

Postgres query - get all records of lowest price per ID

3 Answers3