Get the the product with highest sells in each store - keep getting wrong products but right quantities

Question

For the following table I want to get a table that shows the most sold product in each store Same product can be the most sold in more than 1 store.

This is the query I tried:

SELECT StoreID, ProductID, MAX(Quantity), TotalPrice FROM MyTable GROUP BY StoreID LIMIT 0, 99999;

The answer I get is:

|StoreID|ProductID|MAX(Quantity)|TotalPrice
'1', '1', '566', '288.0000'
'2', '1', '782', '648.0000'
'3', '1', '473', '172.8000'

The problem: I've checked and the max quantities are correct
but for some reason it shows the ID '1' for every product even that it's not the most sold product in any store.

For this table (Called 'MyTable'):

|StoreID|TotalPrice|ProductID|Quantity
'1', '288.0000', '1', '167'
'1', '760.0000', '2', '417'
'1', '400.0000', '3', '70'
'1', '352.0000', '4', '218'
'1', '1105.0000', '5', '117'
'1', '600.0000', '6', '156'
'1', '300.0000', '7', '193'
'1', '960.0000', '8', '84'
'1', '1552.0000', '9', '36'
'1', '558.0000', '10', '115'
'1', '201.6000', '11', '218'
'1', '152.0000', '12', '8'
'1', '9.6000', '13', '187'
'1', '167.4000', '14', '47'
'1', '62.0000', '15', '42'
'1', '417.0000', '16', '451'
'1', '936.0000', '17', '345'
'1', '450.0000', '18', '112'
'1', '7.3000', '19', '68'
'1', '1701.0000', '20', '66'
'1', '80.0000', '21', '337'
'1', '100.8000', '22', '58'
'1', '151.2000', '23', '275'
'1', '43.2000', '24', '431'
'1', '44.8000', '25', '171'
'1', '1245.0000', '26', '297'
'1', '526.5000', '27', '185'
'1', '1528.8000', '28', '127'
'1', '1485.0000', '29', '61'
'1', '124.2000', '30', '158'
'1', '600.0000', '31', '566'
'1', '153.6000', '32', '91'
'1', '120.0000', '33', '294'
'1', '112.0000', '34', '62'
'1', '57.6000', '35', '249'
'1', '456.0000', '36', '200'
'1', '208.0000', '37', '18'
'1', '4216.0000', '38', '86'
'1', '57.6000', '39', '192'
'1', '735.0000', '40', '431'
'1', '123.2000', '41', '220'
'1', '22.4000', '42', '183'
'1', '920.0000', '43', '141'
'1', '325.5000', '44', '281'
'1', '114.0000', '45', '360'
'1', '288.0000', '46', '249'
'1', '418.0000', '47', '156'
'1', '191.2500', '48', '45'
'1', '240.0000', '49', '45'
'1', '406.2500', '50', '25'
'1', '1696.0000', '51', '271'
'1', '112.0000', '52', '146'
'1', '1048.0000', '53', '202'
'1', '59.0000', '54', '180'
'1', '384.0000', '55', '147'
'1', '2128.0000', '56', '428'
'1', '234.0000', '57', '146'
'1', '318.0000', '58', '201'
'1', '3080.0000', '59', '504'
'1', '544.0000', '60', '433'
'1', '364.8000', '61', '221'
'1', '591.0000', '62', '247'
'1', '2808.0000', '63', '187'
'1', '1330.0000', '64', '194'
'1', '336.0000', '65', '250'
'1', '816.0000', '66', '110'
'1', '56.0000', '67', '105'
'1', '30.0000', '68', '245'
'1', '432.0000', '69', '305'
'1', '252.0000', '70', '254'
'1', '344.0000', '71', '326'
'1', '556.0000', '72', '400'
'1', '240.0000', '73', '143'
'1', '400.0000', '74', '70'
'1', '186.0000', '75', '490'
'1', '216.0000', '76', '348'
'1', '104.0000', '77', '255'
'2', '648.0000', '1', '317'
'2', '608.0000', '2', '453'
'2', '160.0000', '3', '128'
'2', '422.4000', '4', '109'
'2', '85.4000', '5', '124'
'2', '150.0000', '6', '77'
'2', '240.0000', '7', '332'
'2', '2240.0000', '8', '222'
'2', '4850.0000', '9', '53'
'2', '396.8000', '10', '435'
'2', '403.2000', '11', '293'
'2', '570.0000', '12', '107'
'2', '96.0000', '13', '477'
'2', '279.0000', '14', '224'
'2', '155.0000', '15', '60'
'2', '139.0000', '16', '294'
'2', '468.0000', '17', '416'
'2', '2000.0000', '18', '218'
'2', '109.5000', '19', '422'
'2', '2592.0000', '20', '219'
'2', '160.0000', '21', '286'
'2', '201.6000', '22', '204'
'2', '180.0000', '23', '187'
'2', '54.0000', '24', '363'
'2', '134.4000', '25', '120'
'2', '249.0000', '26', '164'
'2', '1755.0000', '27', '80'
'2', '1019.2000', '28', '281'
'2', '1980.0000', '29', '458'
'2', '165.6000', '30', '175'
'2', '200.0000', '31', '533'
'2', '1024.0000', '32', '176'
'2', '50.0000', '33', '233'
'2', '156.8000', '34', '158'
'2', '288.0000', '35', '348'
'2', '608.0000', '36', '359'
'2', '468.0000', '37', '18'
'2', '4216.0000', '38', '292'
'2', '604.8000', '39', '317'
'2', '588.0000', '40', '518'
'2', '77.0000', '41', '301'
'2', '112.0000', '42', '223'
'2', '552.0000', '43', '298'
'2', '248.0000', '44', '136'
'2', '380.0000', '45', '40'
'2', '48.0000', '46', '230'
'2', '121.6000', '47', '197'
'2', '153.0000', '48', '15'
'2', '640.0000', '49', '344'
'2', '195.0000', '50', '101'
'2', '1484.0000', '51', '328'
'2', '44.8000', '52', '198'
'2', '393.0000', '53', '320'
'2', '59.0000', '54', '295'
'2', '403.2000', '55', '283'
'2', '121.6000', '56', '486'
'2', '249.6000', '57', '202'
'2', '318.0000', '58', '102'
'2', '660.0000', '59', '630'
'2', '1088.0000', '60', '782'
'2', '2052.0000', '61', '177'
'2', '591.0000', '62', '399'
'2', '280.8000', '63', '223'
'2', '931.0000', '64', '397'
'2', '252.0000', '65', '229'
'2', '408.0000', '66', '95'
'2', '210.0000', '67', '55'
'2', '200.0000', '68', '298'
'2', '518.4000', '69', '232'
'2', '240.0000', '70', '299'
'2', '34.4000', '71', '258'
'2', '667.2000', '72', '200'
'2', '300.0000', '73', '85'
'2', '168.0000', '74', '92'
'2', '37.2000', '75', '542'
'2', '86.4000', '76', '367'
'2', '124.8000', '77', '236'
'3', '172.8000', '1', '344'
'3', '304.0000', '2', '187'
'3', '240.0000', '3', '130'
'3', '1100.0000', '4', '126'
'3', '204.0000', '5', '57'
'3', '120.0000', '6', '68'
'3', '360.0000', '7', '238'
'3', '400.0000', '8', '66'
'3', '582.0000', '9', '6'
'3', '595.2000', '10', '192'
'3', '168.0000', '11', '195'
'3', '364.8000', '12', '229'
'3', '48.0000', '13', '227'
'3', '167.4000', '14', '133'
'3', '248.0000', '15', '20'
'3', '486.5000', '16', '413'
'3', '249.6000', '17', '217'
'3', '600.0000', '18', '209'
'3', '131.4000', '19', '233'
'3', '1814.4000', '20', '28'
'3', '80.0000', '21', '393'
'3', '840.0000', '22', '86'
'3', '288.0000', '23', '118'
'3', '100.8000', '24', '331'
'3', '112.0000', '25', '27'
'3', '597.6000', '26', '292'
'3', '877.5000', '27', '100'
'3', '728.0000', '28', '232'
'3', '990.0000', '29', '227'
'3', '1242.0000', '30', '279'
'3', '150.0000', '31', '298'
'3', '320.0000', '32', '30'
'3', '40.0000', '33', '228'
'3', '224.0000', '34', '286'
'3', '1440.0000', '35', '286'
'3', '380.0000', '36', '246'
'3', '20.8000', '37', '89'
'3', '10540.0000', '38', '245'
'3', '86.4000', '39', '284'
'3', '882.0000', '40', '154'
'3', '192.5000', '41', '460'
'3', '98.0000', '42', '291'
'3', '1472.0000', '43', '141'
'3', '620.0000', '44', '184'
'3', '228.0000', '45', '108'
'3', '144.0000', '46', '69'
'3', '114.0000', '47', '132'
'3', '714.0000', '48', '78'
'3', '560.0000', '49', '131'
'3', '195.0000', '50', '109'
'3', '127.2000', '51', '287'
'3', '112.0000', '52', '156'
'3', '262.0000', '53', '200'
'3', '29.5000', '54', '280'
'3', '2304.0000', '55', '473'
'3', '60.8000', '56', '349'
'3', '312.0000', '57', '86'
'3', '848.0000', '58', '231'
'3', '1320.0000', '59', '362'
'3', '952.0000', '60', '362'
'3', '712.5000', '61', '205'
'3', '472.8000', '62', '437'
'3', '175.5000', '63', '35'
'3', '239.4000', '64', '149'
'3', '672.0000', '65', '266'
'3', '136.0000', '66', '34'
'3', '336.0000', '67', '24'
'3', '100.0000', '68', '256'
'3', '28.8000', '69', '177'
'3', '60.0000', '70', '264'
'3', '516.0000', '71', '473'
'3', '174.0000', '72', '206'
'3', '525.0000', '73', '65'
'3', '288.0000', '74', '135'
'3', '37.2000', '75', '123'
'3', '475.2000', '76', '266'
'3', '156.0000', '77', '300'

Answer 1

It matches because you're grouping by StoreID. MAX is a GROUP BY function, so it returns the MAX(Quantity) for each StoreID.

For ProductID and TotalPrice, the query is just returning the values from the first row for each store, because there's nothing telling it to read the values from the row where Quantity=MAX(Quantity)

TheUknown's query, on the other hand, first finds the MAX(Quantity) for each store, then does a second query to return all the fields from the row where Quantity=MAX(Quantity)

Answer 2

The query you wrote is ambiguous. ProductID is not the product that had the max quantity. Think about this query:

SELECT StoreID, ProductID, MAX(Quantity), MIN(Quantity), TotalPrice 
FROM MyTable 
GROUP BY StoreID LIMIT 0, 99999;

Which ProductID do you expect this query to return? The product that has the max quantity or the product that has the min quantity?

Or this:

SELECT StoreID, ProductID, AVG(Quantity), TotalPrice 
FROM MyTable 
GROUP BY StoreID LIMIT 0, 99999;

Perhaps no product has a quantity that is exactly the average quantity.

This type of SQL query is technically illegal and it's an error in most databases other than MySQL. MySQL allows it, and trusts you to know when you're writing such an ambiguous query.

Tip: Every column of your select-list must be either:

• Inside an aggregate function
• Named in the GROUP BY clause.

Any other column will not return a reliable value.

You're asking for a query that has been dubbed the greatest-n-per-group problem. This question comes up frequently on Stack Overflow, and it has been answered many times. Here's a couple of my past answers that got a lot of upvotes:

• Retrieving the last record in each group
• Fetch the row which has the Max value for a column

Answer 3

Try something like below:

SELECT * 
FROM MyTable WHERE (storeID,Quantity) IN 
( SELECT storeID, MAX(Quantity)
  FROM MyTable 
  GROUP BY storeID
)