Assume a database:
name |group |age
---------------------
aron 1 1
bob 1 2
charlie 2 3
danny 2 4
eugene 3 5
null 4 0
how to achieve a query which would extract
name |group |age
---------------------
bob 1 2
danny 2 4
eugene 3 5
null 4 0
which would pick the person with the greatest age within each group?
you could use join like below. Note that this will bring more than 1 rows from a group if more than 1 have maximum age
select A.* from yourtable A
inner join (
select `group`, max(age) as age from
yourtable
group by `group`
)B
on A.age=B.age and A.`group`=B.`group`
alternately you can also use rank() like
select name, age, `group`
from
(
select name,age,`group`, RANK() OVER (partition by `group` order by age desc) as rank from
yourtable
) T where T.rank=1
