List Duplicate Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List Duplicate Rows

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-10-17 : 11:15:13

I'm using this to find duplicates where a person has the same email but varying firstname and lastnames:

select distinct t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id
from [aren1002].[BOOKING]
as t1 inner join [aren1002].[BOOKING]
as t2 
on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id
where t1.booking_status_id = 330
order by last_name asc

Sample data:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303
4880 Dulcie Abuud dulcie@theiremail.com 303

However it is listing the non duplicate rows, For example: The record with Abuud as the last name, doesn't have any duplicates in the table, so I don't want it listed.

The data should be like this:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 11:25:17

quote:
Originally posted by PeteLeHoq

I'm using this to find duplicates where a person has the same name but varying firstname and lastnames:

select distinct t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id
from [aren1002].[BOOKING]
as t1 inner join [aren1002].[BOOKING]
as t2
on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id
where t1.booking_status_id = 330
order by last_name asc

However it is listing the non duplicate rows, ie the rows where an row which has the same email but no varying email.

Thanks

A little confused by your statement about "a person has the same name but varying firstname and lastnames". Can you show an example?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 03:02:28

You're telling two things

first
a person has the same name but varying firstname and lastnames
and then
where an row which has the same email but no varying email.

which is actual scenario
It would be best to explain with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-10-18 : 05:54:00

Thanks, I've update the post with sample data.

I'm trying to find records where the last name is the same and the email is the same as that record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:00:02

[code]
SELECT booking_id, first_name, last_name, email_add, booking_status_id
FROM
(
select t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id,
COUNT(1) OVER (PARTITION BY t1.email_add) AS Cnt
from [aren1002].[BOOKING]
as t1 inner join [aren1002].[BOOKING]
as t2
on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id
where t1.booking_status_id = 330
)t
WHERE Cnt > 1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources