How to get all fields of a row using SQL MAX function?

Question

Consider this table (from http://www.tizag.com/mysqlTutorial/mysqlmax.php ):

Id name type price 123451 Park Great Hits Music 19.99 123452 Silly Puddy Toy 3.99 123453 Playstation Toy 89.95 123454 Men T-Shirt Clothing 32.50 123455 Blouse Clothing 34.97 123456 Electronica 2002 Music 3.99 123457 Country Tunes Music 21.55 123458 Watermelon Food 8.73 

This SQL query returns the most expensive item from each type: SELECT type, MAX (price) FROM products GROUP BY type

 Clothing $34.97 Food $8.73 Music $21.55 Toy $89.95 

I also want to get the id and name fields, which refer to the above maximum price, for each row. Which SQL query will return the table as follows?

 Id name type price 123455 Blouse Clothing 34.97 123458 Watermelon Food 8.73 123457 Country Tunes Music 21.55 123453 Playstation Toy 89.95 

Answer 1

This is the greatest-n-per-group problem that occurs frequently. My usual way to solve it is logically equivalent to @Martin Smith's answer, but does not use a subquery:

 SELECT T1.Id, T1.name, T1.type, T1.price FROM Table T1 LEFT OUTER JOIN Table T2 ON (T1.type = T2.type AND T1.price < T2.price) WHERE T2.price IS NULL; 

My solution and all the others provided in this thread have a chance to create several lines on the type value if more than one product has the same type and both have the same price, maximum. There are ways to resolve this and break the tie, but you need to tell us which product will “win” in this case.

You need another attribute that is guaranteed to be unique across all lines, at least for lines with the same type . For example, if a product with a high Id value needs to win, you can enable communication in this way:

 SELECT T1.Id, T1.name, T1.type, T1.price FROM Table T1 LEFT OUTER JOIN Table T2 ON (T1.type = T2.type AND (T1.price < T2.price OR T1.price = T2.price AND T1.Id < T2.Id)) WHERE T2.price IS NULL; 

Answer 2

Edit Just update mine to fulfill the specified requirement

 SELECT Id, name, type,price FROM Table T1 WHERE NOT EXISTS( SELECT * FROM TABLE T2 WHERE T1.type=t2.type AND T2.Price >= T1.Price AND T2.Id > T1.Id ) 

Answer 3

You can do this with a subquery

 SELECT id, name, type, price FROM products p1 WHERE EXISTS (Select type, max(price) FROM Products p2 GROUP BY type WHERE p1.type=p2.type AND p1.price=p2.MAX(price)) 

or internal connection

 SELECT id, name, type, price FROM products p1 INNER JOIN (Select type, max(price) FROM Products p2 GROUP BY type) maxPrice ON maxPrice=price=p1.price AND maxPrice.type=p1.price