JOIN Help required - 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

JOIN Help required

Author

Topic

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-29 : 09:21:28

ID First Last Email
111 a a a@gmail.com
112 b b b@gmail.com
113 c c c@gmial.com
114 d d d@gmail.com
119 d d dG@gmail.com
115 f f f@gmail.com

The above is my table and i want only result

114 d d d@gmail.com
119 d d dG@gmail.com

using joins.. how this can be possible ?

Deeps

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-29 : 09:23:14

What are you joining to? You only mention one table.

What are you trying to accomplish? Are you looking for duplicated records?

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-29 : 09:25:31

My apologize,,, i have updated my query .. !!

quote:
Originally posted by TimSman

What are you joining to? You only mention one table.

What are you trying to accomplish? Are you looking for duplicated records?

Deeps

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-29 : 09:31:54

So, are you looking for duplicated data within the table? Are First and Last identical in both rows?

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-29 : 09:35:20

yes I am looking for data where ID should be different but firstand last should be similar.. !!!

quote:
Originally posted by TimSman

So, are you looking for duplicated data within the table? Are First and Last identical in both rows?

Deeps

TimSman
Posting Yak Master

127 Posts

Posted - 2010-10-29 : 09:43:21

WITH myCTE AS
(
SELECT
ID
, First
, Last
, Email
, ROW_NUMBER() OVER(PARTITION BY First, Last ORDER BY First, Last) AS dupeNum
, RANK() OVER(ORDER BY First, Last) AS rowRank
, ROW_NUMBER OVER(ORDER BY First, Last) AS rowNum
)

SELECT
*
FROM
myCTE AS t1
INNER JOIN
(
SELECT
*
FROM
myCTE
WHERE
(dupeNum > 1)
) AS t2
ON t1.rowNum = t2.rowRank

UNION ALL

SELECT
*
FROM
myCTE
WHERE
(dupeNum > 1)

;

This will find all the duplicated rows, assuming First and Last are completely identical for the rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources