CODEWITHSUNDEEP
mysqlsql
blackhawks797
blackhawks797

Reputation: 73

MySQL selecting rows that are not in grouping

I have the following MySQL table of sample data:

    create table prices (
      id INTEGER PRIMARY KEY AUTO_INCREMENT,
      store_id INTEGER NOT NULL,
      item INTEGER NOT NULL,
      price FLOAT NOT NULL
    );

    insert into prices values(1,1,1,1.25);
    insert into prices values(2,1,2,.99);
    insert into prices values(3,2,2,1.20);
    insert into prices values(4,3,3,3.25);
    insert into prices values(5,3,1,1.35);

I am looking for a query to select any store_id that a company would not use to purchase product. For example in prices, store_id 2 would not be used because item 2 can be bought for cheaper from store_id 1.

How would I go about this? Thank you.

Upvotes: 0

Views: 57

Answers (2)

forpas
forpas

Reputation: 164099

With this query:

select item, min(price) minprice
from prices
group by item

you get the minimum price for each item.
If you left join the table prices to the above query and keep only the non matching rows (meaning rows that don't have the cheapest price for that item), you will get all the stores and items that can be found cheaper:

select p.*
from prices p left join (
  select item, min(price) minprice
  from prices
  group by item
) g on g.item = p.item and g.minprice = p.price
where g.item is null

See the demo.
Results:

| id  | store_id | item | price |
| --- | -------- | ---- | ----- |
| 3   | 2        | 2    | 1.2   |
| 5   | 3        | 1    | 1.35  |

If you want only the stores that you would not buy anything from:

select p.store_id
from prices p left join (
  select item, min(price) minprice
  from prices
  group by item
) g on g.item = p.item and g.minprice = p.price
group by p.store_id
having sum(g.item is not null) = 0

See the demo.
Results:

| store_id |
| -------- |
| 2        |

Upvotes: 2

DanielGS
DanielGS

Reputation: 109

SELECT *, MIN(price) from prices group by item

Gives you the store_id which has the cheapest item.

Example output:

id | store_id | item | price | MIN(price)

1 | 1 | 1 | 1.25 | 1.25

2 | 1 | 2 | 0.99 | 0.99

4 | 3 | 3 | 3.25 | 3.25

Upvotes: 0

Related Questions