CODEWITHSUNDEEP
sqlpostgresql
Somnath Dutta
Somnath Dutta

Reputation: 87

Showing only duplicate rows from table in postgres

I have table like this:

--------------------------------------
| id | name | phone_number | address |
--------------------------------------
| 1  | Ram  | 9090909090   | Delhi   |
| 2  | Shyam| 9865444456   | Mumbai  |
| 3  | Mohan| 9756543455   | Chennai |
| 4  | Ram  | 9090909090   | Delhi   |
--------------------------------------

I want to return the rows having same column data. The result will be like this:

--------------------------------------
| id | name | phone_number | address |
--------------------------------------
| 1  | Ram  | 9090909090   | Delhi   |
| 4  | Ram  | 9090909090   | Delhi   |
--------------------------------------

Upvotes: 0

Views: 68

Answers (4)

Subin Chalil
Subin Chalil

Reputation: 3660

SELECT T1.*
FROM
 table_name T1
 INNER JOIN table_name T2 ON 
      T1.name= T2.nam` AND 
         T1.phone_number= T2.phone_number AND T1.address= T2.address 
 WHERE T2.id <> T1.id

Upvotes: 0

user330315
user330315

Reputation:

This can be done using window functions which avoids the join on the aggregated data and is usually the faster way:

select *
from (
  select id,name,phone_number,address
         count(*) over (partition by name,phone_number,address) as cnt
  from the_table
) t
where cnt > 1;

Upvotes: 2

Tabish Khan
Tabish Khan

Reputation: 38

Please run the below query, (consider table name to be "data"), to get the desired result as follows:

SELECT * FROM data where name IN (SELECT name FROM data GROUP BY name HAVING COUNT(*) > 1);

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT t2.id,t2.name,t2.phone_number,t2.address
FROM
(
SELECT name,phone_number,address
FROM tableName
GROUP BY name,phone_number,address
HAVING COUNT(*) > 1
) AS t1
INNER JOIN tableName t2
ON t1.name=t2.name AND t1.phone_number=t2.phone_number AND t1.address=t2.address

Upvotes: 1

Related Questions