Select a row by max(year) and group by name, but also include other values in the result set

Question

i have a problem with a specific selection in a table. In would like to find all Persons grouped by Name with the max year. But I need also the other columns, for a later join, in the result set.

Furthermore, the id is unordered, so it can not be used in an aggregate function. The query should be database agnostic.

Here is the Table:

Here is the Result that I need:

A lot has changed since SQL-92. Why specifically SQL-92? What RDBMS are you using? https://modern-sql.com/blog/2018-04/mysql-8.0 — Michael Buen, Apr 05 '19 at 07:19
The query is a part of a native query in an java application. Currently we are supporting SQLServer, Oracle, Postgres — Andreas Hauschild, Apr 05 '19 at 07:26

score 1 · Accepted Answer · answered Apr 05 '19 at 07:05

1

You can try below- using correlated subquery

select * from tablename a
where year = (select max(year) from tablename b a.name=b.name)

answered Apr 05 '19 at 07:05

Fahmi

37,315
5
22
31

1

Thx for your support! It Helped. – Andreas Hauschild Apr 05 '19 at 08:00

Michael Buen · Answer 2 · 2019-04-05T08:02:42.077

1

select x.* from tbl x
join (select name, max(year) max_year from tbl group by name) y
on x.name = y.name and x.year = y.max_year

edited Apr 05 '19 at 08:02

answered Apr 05 '19 at 07:21

Michael Buen

38,643
9
94
118

Thx for your support! It Helped. – Andreas Hauschild Apr 05 '19 at 08:00

Select a row by max(year) and group by name, but also include other values in the result set

2 Answers2