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 |
+----+-------+-------+------+-------+
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