By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tableName (
id int ,
address_id int,
state varchar(5)
);
INSERT INTO tableName values
(12,1,'A'),
(94,1,'A'),
(991,1,'A'),
(992,2,'A'),
(993,2,'A'),
(37,4,'A');
CREATE TABLE
INSERT 0 6
select max(id) as id ,address_id,state
from (
SELECT id, address_id,state
, count(*) OVER ( PARTITION BY address_id ) AS cnt
FROM tableName
where state='A'
) as t1
where cnt>1
group by address_id,state;
| id | address_id | state |
|---|---|---|
| 991 | 1 | A |
| 993 | 2 | A |
SELECT 2