CODEWITHSUNDEEP
sqlpostgresql
Ahmet DAL
Ahmet DAL

Reputation: 4700

Select field of record contains max of another field

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions