SQL GROUP BY AND MAX

Question

I have the following table in my database:

-------------------------------------------
| value  | category |         date        |
-------------------------------------------
| 12.2   | A        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 13.3   | A        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 11.0   | B        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 11.2   | C        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 19.2   | C        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 10.4   | C        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------

And i'm trying to get the Maximum Value(12.2, 13.3,..) and the date correspondant to this value of each Category(A, B, C).

Like this for example:

-------------------------------------------
| value  | category |         date        |
-------------------------------------------
| 13.3   | A        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 11.0   | B        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------
| 19.2   | C        | yyyy-MM-dd HH:mm:ss | 
-------------------------------------------

I have followed this link SQL: How to select a max value for each group per day?

So I made this query:

SELECT MAX(value), category, date FROM myTable GROUP BY category;

But for some reason i don't know why it doesn't gave me what i'm looking for, it gave's me a wrong value of the DATE (DATE does not correspondant to the Max(value))!!!

Answer 1

But for some reason i don't know why it doesn't gave me what i'm looking for, it gave's me a wrong value

The reason it didn't give you the correct values in this case:

SELECT MAX(value), category, date FROM myTable GROUP BY category;

is that, when you include a columns in the select clause in mysql that are not included in the group by nor an aggregate function like date, mysql selects an arbitrary values for it, so that it gives you the wrong values not the values that corresponds to the max values.

---

And i'm trying to get the Maximum Value(12.2, 13.3,..) and the date correspondant to this value of each Category(A, B, C).

To get the dates that are correspondant to the MAX(value) try this instead:

SELECT t1.*
FROM myTable AS t1
INNER JOIN
(
  SELECT MAX(value) AS MaxV, category
  FROM myTable AS t1
  GROUP BY category
) AS t2 ON  t2.MaxV     = t1.value
        AND t1.Category = t2.category;

The join to the subquery will ensure that the returned date value correspondant to the Max(value) and will eliminate the other values.

• SQL Fiddle Demo

Answer 2

You can do this using a sub qyery.

SELECT MAX(t.value), 
    t.category,
    (SELECT m.date FROM myTable as m 
     WHERE m.value = MAX(t.value) AND m.category = t.category) as Category
FROM myTable as t
GROUP BY category;