CODEWITHSUNDEEP
sql
Adity Singh
Adity Singh

Reputation: 23

Fetch all duplicate rows

I want to fetch only duplicate rows with repetition of values in name column occurring multiple times without the count column and I don't want to group them. Examples given below. e.g.

for example this is table:-

    id |  name  | project 
----+--------+---------
  1 | aditya | java
  2 | aditya | cloud
  3 | bradly | go
  4 | cooper | java
  5 | alexa  | elixir 
  6 | jason  | search
  7 | jason  | java

result should be :-

   id |  name  | project 
----+--------+---------
  1 | aditya | java
  2 | aditya | cloud
  6 | jason  | search
  7 | jason  | java

Upvotes: 1

Views: 109

Answers (2)

Rajat
Rajat

Reputation: 5803

You could also either use a join or an in

select a.*
from t a
inner join t b on a.name=b.name and a.id<>b.id

OR

select *
from t
where name in (select name from t group by name having count(*)>1);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

A simple method is to use exists:

select t.*
from t
where exists (select 1 from t t2 where t2.name = t.name and t2.id <> t.id)
order by name;

You can also use window functions:

select t.*
from (select t.*, count(*) over (partition by name) as cnt
      from t
     ) t
where cnt > 1
order by name;

Upvotes: 3

Related Questions