Reputation: 4700
I am using PostgreSQL version 9.x
I want to select a field of a record that contains max value of a filed in grouped items. Assume that these are my table and content;
id | name | surname | age
1 ahmet adal1 25
2 mehmet mdal2 23
3 ahmet adal3 27
4 ahmet adal4 22
5 mehmet mdal5 28
I want to group all records by name and select surname of the record that contains max age.
I should get as result;
adal3
mdal5
here is a query that I want to run;
select p.surname
from person p
group by name
having max(age)
I know this is not working and having expression must be boolean. I gave this just to explain myself better.
Is there a way to get what I want with sql, especially PostgreSQL?
Thank you!
Upvotes: 0
Views: 68
Reputation: 1271231
Postgres has the syntax distinct on
that does this:
select distinct on (name) p.surname
from person p
order by name, age desc
Upvotes: 1