CODEWITHSUNDEEP
mysqlgroup-bymax
Atari2600
Atari2600

Reputation: 1239

MySQL Group By - Get row associated with max() amount

What I am trying to get is the row contents containing the highest price. I am not sure if I need to self-join, or use a having statement. Most of the attempts I have tried netted me no returned rows. Effectively I want to say "WHERE PRICE IS MAX".

I have the following SQL 

SELECT * FROM table

GROUP BY Store, Date, Item

Starting with:

+----+-------+-------+------+-------+
| id | Store | Date  | Item | Price |
+----+-------+-------+------+-------+
|  1 | A     | 1-Jan |  123 | 0.5   |
|  2 | A     | 1-Jan |  123 | 0.75  |
|  3 | A     | 2-Jan |  456 | 0.45  |
|  4 | B     | 1-Jan |  123 | 0.85  |
|  5 | B     | 1-Jan |  123 | 0.75  |
|  6 | B     | 2-Jan |  778 | 0.45  |
+----+-------+-------+------+-------+

I want to end up with

+----+-------+-------+------+-------+
| id | Store | Date  | Item | Price |
+----+-------+-------+------+-------+
|  2 | A     | 1-Jan |  123 | 0.75  |
|  4 | B     | 1-Jan |  123 | 0.85  |
|  6 | B     | 2-Jan |  778 | 0.45  |
+----+-------+-------+------+-------+

Upvotes: 0

Views: 62

Answers (1)

winmutt
winmutt

Reputation: 405

What you are looking for is affectionately known as a groupwise max:

http://jan.kneschke.de/projects/mysql/groupwise-max/ https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Upvotes: 1

Related Questions