I have a table
date name
2014-01-01 AAA
2014-01-01 BBB
2014-01-01 CCC
2014-01-01 DDD
2015-05-05 AAA
2016-09-09 AAA
2016-09-09 BBB
and I want to have only the newest information about every person:
date name
2016-09-09 AAA
2016-09-09 BBB
2014-01-01 CCC
2014-01-01 DDD
I can simply write
SELECT MAX(date), name
FROM table1
GROUP BY name;
but if I want to add another column, it doesn't work
SELECT MAX(date), name, address
FROM table1
GROUP BY name;
(doesn't work)
SELECT MAX(date), name, address
FROM table1
GROUP BY name, address;
(it works, but I want to have only one record for one person)
How can I do it?
Assuming your dates for each name are unique, it's quite simple to solve using a derived table and join, like this:
SELECT maxdate, t.name, t.address
from table1 t
inner join
(
SELECT MAX(date) as maxdate, name
FROM table1
GROUP BY name
) d on(t.date = d.maxdate and t.name = d.name)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments