Couldn't really find a suitable title so I will explain here. I have two tables: Citizens and Sects. The relevant columns for this question are as such :
Citizens: full_name, age, power_rank , sect_id.
Sects: ID, Sect_name.
What I want to do, is add a column to Sects which I will name 'Sect_Master'. A citizen is qualifiable to be a sect_master if they are of power_rank 9 , and in the case there are multiple ones, We will choose the oldest and assign him as sect_master in the Sects table.
So my question is basically how to get a list of only one person by sect who is level 9 and the oldest available ?
EDIT:
Looks like this is the answer:
WITH possible AS (
SELECT c.sect_id, c.full_name, c.age, ROW_NUMBER() OVER (PARTITION BY sect_id ORDER BY age DESC) AS rn
FROM citizens AS c WHERE power_rank = 9
)
SELECT * FROM possible WHERE rn = 1;
