Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE items (
    item_id   SERIAL
  , title     VARCHAR(40)
  , item_code VARcHAR(40)
  , price     NUMERIC
  , site_id   INTEGER
  , store_id  INTEGER
);

INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Shampoo', 'TEST1', 10, 1, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Shampoo', 'TEST1',  5, 2, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Shampoo', 'TEST1', 12, 2, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Conditioner', 'TEST2',  9, 1, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Conditioner', 'TEST2',  4, 2, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Conditioner', 'TEST2',  7, 2, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Conditioner', 'TEST2',  4, 2, 1 );
INSERT INTO items ( title, item_code, price, site_id, store_id ) VALUES ( 'Conditioner', 'TEST2',  6, 2, 1 );
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

    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
         ;


item_id
title
item_code
price
site_id
store_id


2
Shampoo
TEST1
5
2
1

5
Conditioner
TEST2
4
2
1

7
Conditioner
TEST2
4
2
1