I have problem with my postgres sql query. I want to find duplicates with specific conditions.

The conditions : I want to find the duplicates based on address_id and they should have state 'A'

So I wrote query :

select count(*), g.address_id
            from tableName g
            where g.state = 'A'
            group by g.address_id
            having count(*)
                       > 1

When I want to get all values I just extended query to :

SELECT w.* from tableName w
    (select count(*), g.address_id
                from tableName g
                where g.state = 'A'
                group by g.address_id
                having count(*)
                           > 1) x on w.address_id = x.address_id

In output I get list of all duplicates. But I want to get duplicates with the highest id.

Based on my table i want to get output :

CodePudding user response：

You can use exists to check for duplicates group by address_id and join or use in:

select *
from tableName
where id in (
    select max(id)
    from tableName tn
    where exists(select * from tableName itn where tn.address_id = itn.address_id 
    and tn.id <> itn.id)
    group by address_id
);

or

select otn.*
from tableName otn
join (
    select max(id) id
    from tableName tn
    where exists(select * from tableName itn where tn.address_id = itn.address_id 
    and tn.id <> itn.id)
    group by address_id
) groupped on otn.id = groupped.id;

CodePudding user response：

Another way :

select tn.id,tn.address_id,tn.state
from tableName tn
inner join (select max(id) as id ,count(address_id) as nr_count
            from tableName
            where state='A'
            group by address_id
            ) as t1 on tn.id=t1.id
where   t1.nr_count >1;   

Demo

You could use window function:

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;    

Demo