Postgres query - get all records of lowest price per ID-CodePudding

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

CodePudding user response：

Use DISTINCT ON:

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

See the demo.

CodePudding user response：

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

CodePudding user response：

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;