CODEWITHSUNDEEP
sql-server-2008
Dinup Kandel
Dinup Kandel

Reputation: 2505

need to show only the duplicate value in sql

i have table as

id----name----roll-----class
 1----ram-------1-----2
 2----shyam-----2-----3   
 3----ram-------1-----3
 4----shyam-----2-----3
 5----ram-------1-----2
 6----hari------1-----5

i need to find the the duplicate row only that have common name, roll, class. so the expected result for me is.

    id----name----roll-----class
     1----ram-------1-------2
     2----shyam-----2-------3           
     4----shyam-----2-------3
     5----ram-------1-------2

i tried to get from the query below but here only one field is supported. i need all three field common. Please do help me in this. thanks

SELECT * 
FROM table 
WHERE tablefield IN ( 
 SELECT tablefield
 FROM table 
 GROUP BY tablefield  
 HAVING (COUNT(tablefield ) > 1) 
)

Upvotes: 0

Views: 1208

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You can use count() over().

select id, name, roll, class
from (select id, name, roll, class,
             count(*) over(partition by name, roll, class) as c
      from YourTable) as T
where c > 1
order by id

https://data.stackexchange.com/stackoverflow/query/63720/duplicates

Upvotes: 1

unludo
unludo

Reputation: 5010

Something like that should work (I did not test though):

select a1.* 
from table a1, a2 
where (a1.id != a2.id) 
  and (a1.name == a2.name) 
  and (a1.roll== a2.roll)   
  and (a1.class== a2.class);

It seems there are several proprosals here. If it is a query that you'll use in your code, beware of the cost of the queries. Try an 'explain' with your database.

Upvotes: 0

Alex
Alex

Reputation: 23300

I'd suggest something like this

SELECT A.* FROM
Table A LEFT OUTER JOIN Table B
ON A.Id <> B.Id AND A.Name = B.Name AND A.Roll = B.Roll AND A.Class = B.Class
WHERE B.Id IS NOT NULL

Upvotes: 0

Vikram
Vikram

Reputation: 8333

this will retun only the duplicate entry one time:

select t.id, t.name, t.roll, t.class 
from table t 
inner join table t1 
on t.id<t1.id 
and t.name=t1.name 
and t.roll = t1.roll 
and t.class=t1.class

this will return what you require:

select distinct t.id, t.name, t.roll, t.class 
    from table t 
    inner join table t1 
    on t.name=t1.name 
    and t.roll = t1.roll 
    and t.class=t1.class

Upvotes: 0

Related Questions