MYSQL Min by Group and Distinct

[joecrocker]

I'm a newbie and have been struggling on this MYSQL query for days...

I would like to display the min price for each store_id (group) and it's associated fields. This query works fine when item_id = 70 but not for any other item_ids. Only one row of data (one store) is displayed instead of two stores.

Any help would be greatly appreciated!!! Thanks!!!!


GOOD QUERY when item_id = 70:
SELECT min1.* FROM toy_items AS min1 LEFT OUTER JOIN toy_items AS min2 ON min2.store_id = min1.store_id AND min2.price < min1.price WHERE min2.price IS null AND min1.item_id = 70 ORDER BY min1.price

CORRECT RESUTLS:
id item_id store_id description price quantity
363 70 14 Toys for tots 1 0.55 21
366 70 15 Toys for tots 41 0.75 52

WRONG QUERY when item_id = 78:
SELECT min1.* FROM toy_items AS min1 LEFT OUTER JOIN toy_items AS min2 ON min2.store_id = min1.store_id AND min2.price < min1.price WHERE min2.price IS null AND min1.item_id = 78 ORDER BY min1.price

WRONG RESUTLS (store 14 result should also be displayed):
id item_id store_id description price quantity
368 78 15 Toys for tots 44 0.75 26


Here's the table and data:
CREATE TABLE IF NOT EXISTS `toy_items` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT,
`item_id` int(4) unsigned NOT NULL DEFAULT '0',
`store_id` int(4) unsigned NOT NULL DEFAULT '0',
`description` VARCHAR(255) NOT NULL DEFAULT '',
`price` DECIMAL(15,2),
`quantity` int(4) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO toy_items (id, item_id, store_id, price, description, quantity) VALUES
('363','70', '14', '0.550', 'Toys for tots 1', '21'),
('364','84', '14', '1.440', 'Toys for tots 11', '22'),
('365','78', '14', '0.690', 'Toys for tots 31', '23'),
('366','70', '15', '0.750', 'Toys for tots 41', '52'),
('367','84', '15', '0.750', 'Toys for tots 16', '25'),
('368','78', '15', '0.750', 'Toys for tots 44', '26'),
('369','70', '15', '0.950', 'Toys for tots 3', '28'),
('370','84', '15', '0.950', 'Toys for tots 11', '1'),
('371','78', '15', '0.950', 'Toys for tots 98', '4');

[Old Pedant]

And what do you do if there are more than one item all with the same minimum price at a given store??

*ASSUMING* that prices are unique--no duplicates, at least on a per store, per item basis--it's not hard:

SELECT T.id, T.item_id, T.store_id, T.price, T.description, T.quantity
FROM toy_items AS T,
( SELECT store_id, item_id, MIN(price) AS minprice
FROM toy_items
GROUP BY store_id, item_id ) AS M
WHERE T.store_id = M.store_id
AND T.item_id = M.item_id
AND T.price = M.minprice

But if you do have multiple records with the same store and item and with the same minimum price, that query will return *all* of them for that store.

If you only want one of them, then you need to tell us how to break the ties: Which one should we select in case of a tie?

*********

EDIT: Sorry, just noticed that returns *ALL* items for all stores. Obviously, if you wanted to restrict it to just one item_id -- or maybe to a set of item_id's -- you would need to add an additional condition in the WHERE. It might be more efficient to add it to the inner select, thus:

SELECT T.id, T.item_id, T.store_id, T.price, T.description, T.quantity
FROM toy_items AS T,
( SELECT store_id, item_id, MIN(price) AS minprice
FROM toy_items
WHERE item_id IN ( 70, 78 )
GROUP BY store_id, item_id ) AS M
WHERE T.store_id = M.store_id
AND T.item_id = M.item_id
AND T.price = M.minprice

You could have as few or as many item_id's inside that "IN (...)" as needed.

[Old Pedant]

How come MySQL people always seem to put '...' around NUMERICAL values in queries???

In some DBs, that's treated as a hard error: You get a type mismatch error. In MySQL, it works, but it causes MySQL to have to do a CAST to the correct type.

I see this all the time in MySQL posts but almost never in queries for Access and SQL Server databases and I think never for Oracle (maybe Oracle is most strict??).

In this instance, why not:

INSERT INTO Toy_items (id, item_id, store_id, price, description, quantity) VALUES
(363,70, 14, 0.550, 'Toys for tots 1', 21),
(364,84, 14, 1.440, 'Toys for tots 11', 22),
(365,78, 14, 0.690, 'Toys for tots 31', 23),
(366,70, 15, 0.750, 'Toys for tots 41', 52),
(367,84, 15, 0.750, 'Toys for tots 16', 25),
(368,78, 15, 0.750, 'Toys for tots 44', 26),
(369,70, 15, 0.950, 'Toys for tots 3', 28),
(370,84, 15, 0.950, 'Toys for tots 11', 1),
(371,78, 15, 0.950, 'Toys for tots 98', 4);

[joecrocker]

Thank You Old Pedant!!! This is exactly what I needed!!

[joecrocker]

In my case it's just how it was presented to me in examples my SQL book "Sams Teach Yourself SQL in 24 hours". Didn't know you could insert NUMERICAL values without the '...' .....

[Old Pedant]

Ahhh...well, if a pretty standard book like that makes that mistake, then it would explain why I see it so often! Fascinating! Thanks! First time somebody has told me where they got the notion of using apostrophes around numbers.