| id | year |
|---|---|
| 1 | 2014 |
| 10 | 2015 |
| 10 | 2019 |
| 102 | 2015 |
| 102 | 2019 |
| 104 | 2015 |
| 104 | 2017 |
| 104 | 2019 |
| 104 | 2021 |
The output I want in postgres is below. The max year is populated based on the id and the count should also count based on id. If id = 10 then it should show the max date within id 10 and also count how many records have the id as 10.
| id | year | max year | count |
|---|---|---|---|
| 1 | 2014 | 2014 | 1 |
| 10 | 2015 | 2017 | 2 |
| 10 | 2017 | 2017 | 2 |
| 102 | 2015 | 2019 | 2 |
| 102 | 2019 | 2019 | 2 |
| 104 | 2015 | 2021 | 4 |
| 104 | 2017 | 2021 | 4 |
| 104 | 2019 | 2021 | 4 |
| 104 | 2021 | 2021 | 4 |
SELECT aa.id,
aa.year,
aa.max_year,
count(aa.id)
from (SELECT id,MAX(year) AS year FROM table
GROUP BY id) aa
FULL JOIN table2 b ON aa.id = b.id
CodePudding user response：
You can use window functions:
select id,
year,
max(year) over (partition by id) as max_year,
count(*) over (partition by id)
from the_table