-2

I have this table

id|name|number|date
1|peter|2|2017-09-18
2|peter|1|2017-10-03
3|james|4|2017-09-05
4|james|1|2017-10-10
5|james|0|2017-10-15
6|kate|4|2017-09-16
7|kate|2|2017-10-17

I want to select the latest row for each person (the row with the latest date). The result will look like

2|peter|1|2017-10-03
5|james|0|2017-10-15
7|kate|2|2017-10-17

Which MYSQL query can do that?

Something like :

SELECT id, name, number, date 
FROM table 
GROUP BY id, name, number, date 
HAVING date the latest
J. Neal
  • 71
  • 9

3 Answers3

0
select t1.* from table t1, (select t2.name name2, max(t2.date) 
date2 from table t2 group by t2.name) t3 where t1.name = t3.name2 and t1.date = t3.date2
Mario Lopes
  • 125
  • 7
0

SELECT * FROM persons WHERE name='someone' order by date desc limit 1

This works well for selecting lastest entity by specified name.

SELECT * FROM persons GROUP BY name, date ORDER BY date DESC

I wrote this with my mobile, something can be wrong, but its concepts will be correct.

jay k
  • 13
  • 5
0
MariaDB [test]> select * from stack s1 where s1.d = (select max(s2.d) 
from stack s2 where s2.name = s1.name);
+------+-------+------+------------+
| id   | name  | num  | d          |
+------+-------+------+------------+
|    2 | peter |    1 | 2017-10-03 |
|    5 | james |    0 | 2017-10-15 |
|    7 | kate  |    2 | 2017-10-17 |
+------+-------+------+------------+
3 rows in set (0.00 sec)

And for large datasets, don't forget to add indexes to 'name' and 'd' columns.

Huiser
  • 1
  • 1