CODEWITHSUNDEEP
mysqljoin
Soujirou
Soujirou

Reputation: 109

Getting the latest date for mysql rows

Sorry for my bad description but I'm trying to get the latest date for each person that last ate each fruit. I don't think I'm grouping it right or there is something I missed totally. Can someone help me with this? Thanks in advance.

T1
+--------+--------+--------+
| person | fruit  |  date  |
+--------+--------+--------+
|  Bob   | apple  | 1-1-13 |
|  Bob   | apple  | 1-4-13 |
|  Bob   | apple  | 1-6-13 |
|  Bob   | orange | 1-2-13 |
|  John  | apple  | 1-3-13 |
|  John  | orange | 1-2-13 |
|  John  | orange | 1-4-13 |
|  John  | orange | 1-9-13 |
+--------+--------+--------+

Results I'm looking for
+--------+--------+--------+
| person | fruit  |  date  |
+--------+--------+--------+
|  Bob   | apple  | 1-6-13 |
|  Bob   | orange | 1-2-13 |
|  John  | apple  | 1-3-13 |
|  John  | orange | 1-9-13 |
+--------+--------+--------+

What I tried from reading similar posts.

SELECT * FROM T1 
  JOIN (
    SELECT person,max(date) AS latest 
      FROM T1 GROUP BY person
    ) T2
  ON T1.person = T2.person AND T1.date = T2.latest

Resulting in
+--------+--------+--------+
| person | fruit  |  date  |
+--------+--------+--------+
|  Bob   | apple  | 1-6-13 |
|  John  | orange | 1-9-13 |
+--------+--------+--------+

How do I get it to separate the fruit column too?

Upvotes: 0

Views: 59

Answers (1)

Kermit
Kermit

Reputation: 34055

No need for a JOIN here. Simple aggregate with a GROUP BY.

SELECT person, fruit, DATE_FORMAT(MAX(date), '%c-%e-%y') AS max_date
FROM T1
GROUP BY person, fruit

Result

| PERSON |  FRUIT | MAX_DATE |
------------------------------
|    Bob |  apple |   1-6-13 |
|    Bob | orange |   1-2-13 |
|   John |  apple |   1-3-13 |
|   John | orange |   1-9-13 |

See it in action

Upvotes: 2

Related Questions