Social Icons

Friday, April 13, 2012

How to find the dupliate record in sql w.r.t columns

You can find the duplicate record using the folloing query with respect to columns sal and age
if you have Users table
LoginId Password UserName Sal Age
admin 1153291460 Khalid Rafique 1100.00000000 25
asdf tyro1 aa 1200.00000000 28
tyro tyro1 Test Account 1200.00000000 28
tyro23 tyro1 Test Account 1200.00000000 28

One Way
select u1.* from Users u1 
inner join
  (select Sal, Age,COUNT(*)as [Count] from Users
   group by Sal,Age
   having COUNT(*)>1) 
as t1
on u1.Sal = t1.Sal and u1.Age =t1.Age


2nd Way
select * from Users
where CAST(Sal as varchar)+'-'+CAST(Age as varchar) in
(select CAST(Sal as varchar)+'-'+CAST(Age as varchar) from Users
 group by Sal,Age
 having COUNT(*)>1)


following will be the result of Query:

LoginId
Password
UserName
Sal
Age
asdf
tyro1
aa
1200.00000000
28
tyro
tyro1
Test Account
1200.00000000
28
tyro23
tyro1
Test Account
1200.00000000
28

No comments:

Post a Comment

 

Sample text

Sample Text