0

I have a table of this type:

table : people
+-------+-----------+-----------+-------------------+
|   id  |   name    |   amount  |       date        |
+-------+-----------+-----------+-------------------+
|   1   |   John    |      12   |    2011-12-03     |
|   2   |   John    |       7   |    2016-02-01     |
|   3   |   Mary    |       0   |    2014-06-23     |
|   4   |   John    |       7   |    2016-01-01     |
|   4   |   Mary    |       5   |    2014-06-01     |
|   4   |   Bill    |       1   |    2010-08-14     |
+-------+-----------+-----------+-------------------+

I need to write a query to select the record with the highest date for each name.

The result will be:

+-----------+-----------+-------------------+
|   name    |   amount  |       date        |
+-----------+-----------+-------------------+
|   John    |       7   |    2016-02-01     |
|   Mary    |       0   |    2014-06-23     |
|   Bill    |       1   |    2010-08-14     |
+-----------+-----------+-------------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Tomi2001
  • 1
  • 1
  • select name,max(date) as date from people group by name – JYoThI Jun 03 '16 at 11:31
  • Have a look at this ste by step guide: http://code.openark.org/blog/mysql/selecting-a-specific-non-aggregated-column-data-in-group-by – Alex Jun 03 '16 at 11:35
  • I'm sorry , it is explained in a previous thread. One possible solution would be :`SELECT people.* FROM people WHERE people.date IN ( SELECT MAX(people.date) FROM people GROUP BY people.name) ORDER BY people.id` – Tomi2001 Jun 07 '16 at 10:03

0 Answers0