CODEWITHSUNDEEP
mysqlsql-server
user9241790
user9241790

Reputation:

Compare two rows in the same table for equality in SQL

I have the following table in my database:

Table name: INSURANCE TABLE

ID | Policy 
1  | 34564  
2  | 67548  
3  | 34564  
4  | 98271  
5  | 90198  
6  | 98271

I am looking for a sql query that will compare the Policy column values in all 5 rows and return those rows which have a value equal to atleast one other row.

For the table above I should get the following result set:

1 | 34564

3 | 34564
4 | 98271
6 | 98271

I would appreciate responses on how to write this query.

Upvotes: 1

Views: 4740

Answers (1)

Elias Toivanen
Elias Toivanen

Reputation: 848

If I understand you, you want to group by Policy, discarding unique values:

select * from your_table where Policy in (
    select Policy from your_table group by Policy having count(*) > 1
);

Upvotes: 4

Related Questions