Select duplicate in postgresql

Question

I have table in postgresql with columns District and tehsil. Tehsil names are not unique and tehsil with same name belong to multiple districts. I want to find out the tehsils with same name belonging to different districts..

Tehsil  District

Alot    Ratlam
Alur    Hassan
Alur    Kurnool
Aluva   Ernakulam
Khanapur    Adilabad
Khanapur    Belgaum
Khanapur    Sangli
Khandaghosh Barddhaman
Khandagiri  Khordha

Madhivanan · Accepted Answer · 2015-08-12 07:00:52Z

1

Try this

select Tehsil from table
group by Tehsil 
having min(District)<>max(District)

To know which district they belong to

select t1.Tehsil, t1.district from table as t1 inner join 
(
select Tehsil from table
group by Tehsil 
having min(District)<>max(District)
) as t2 on t1.Tehsil =t2.Tehsil

edited Aug 12, 2015 at 7:00

answered Aug 12, 2015 at 6:34

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user19120 Over a year ago

I also want to find out to which district those tehsils belong..kindly guide

Mukesh Kalgude · Accepted Answer · 2015-08-12 06:35:28Z

1

Try this way

select Tehsil from table
group by Tehsil 
having count(Tehsil)>1

answered Aug 12, 2015 at 6:35

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

Collectives™ on Stack Overflow

Select duplicate in postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related