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

Question

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

Id     name               type     price 
123451 Park's Great Hits  Music    19.99 
123452 Silly Puddy        Toy      3.99 
123453 Playstation        Toy      89.95 
123454 Men's 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 fields id and name that belong to the above max price, for each row. What SQL query will return a table like this?

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

Athirst

can you tag your question appropriately for the DBMS that you are using? – Perice

How should ties be handled? Do you want just one item per type, or multiple items if there's a tie for most expensive? – Pictograph

@Steve, just one item per type. – Athirst

Answer 1

This is the greatest-n-per-group problem that comes up frequently. My usual way of solving it is logically equivalent to the answer given by @Martin Smith, 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 others given on this thread so far have a chance of producing multiple rows per value of type, if more than one product shares the same type and both have an equal price that is the max. There are ways to resolve this and break the tie, but you need to tell us which product "wins" in case like that.

You need some other attribute that is guaranteed to be unique over all rows, at least for rows with the same type. For example, if the product with the greater Id value should win, you can resolve the tie 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;

Theoretical

Thanks Bill, the second query was exactly what I was searching for. It is a small query but complicated for me. – Athirst

thanks for continuing to answer this question every time is pops up. I forget how to do this every 3 months or so and like your self-join solution a lot. – Sixtieth

@TMG: You might like my soon-to-be-released book, SQL Antipatterns [is.gd/b40bn]. I have a chapter on GROUP BY. You could put a bookmark on the page with this outer join solution! :-) – Theoretical

Answer 2

Edit Just updating mine to meet the clarified 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
          )

Perice

+1 Ah yes, I've done it like this before, but so sql only a few times a years, I awlays forget the greater than trick to ensure you've got the maximum. – Unopened

Unfortunately I cannot upvote unless you edit your answer... would add a space, pls? :) – Reinforcement

Thanks Martin, but if there are more than one item with the same max price and type it returns both of them, but I only need one. The same result returns the query from mdma bellow – Athirst

I will also set this answer as accepted, because now it does not contain dublicates, but It performs slower than the suggested query of Bill Karvin with the OUTER JOIN. In a table of around 5000 records, the NOT EXISTS query takes 360ms to excecute. Bill Karwin's OUTER JOIN query takes 140ms to execute. – Athirst

@yiannis As you've probably discovered you can only mark one accepted answer! I'm not bothered about that but an interesting result. Do you get the same effect regardless of which order you run them in (i.e. could caching be playing a role) – Perice

Answer 3

You can do it with a subselect

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 an inner join

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

Unopened

Thanks mdma, but if there are more than one items with the same max price and type it returns both of them, but I only need one – Athirst

Ok. I see there is a good answer already chosen, so there's no need updating this. – Unopened