January 11, 2010 at 7:03 am
I have a table with three columns:
UserID
LastName
FirstName
I need to create a query or sp that can identify values of the UserID that are duplicated, that is the same UserID has been used in more than one row, but with different LastName values. Then, I need it to also return the UserID, LastName, and FirstName for all of the rows meeting those conditions.
I need to do this in a single t-sql query or sp and without a temp table.
Thank you!
January 11, 2010 at 7:22 am
Untested, since there are no data provided to test against:
;with cte as
(
select UserID
from table
group by UserID
having count(*) >1
)
select table.* from table inner join cte on cte.UserID = table.UserID
January 11, 2010 at 7:22 am
Something like this ?
with cteuser
as
(
Select user_id,LastName,FirstName,row_number() over (partition by user_id order by LastName) as RowN
from <yourtable>
)
Select * from cteUser where RowN > 1
January 11, 2010 at 7:28 am
DROP TABLE #Users
CREATE TABLE #Users (UserID INT, LastName VARCHAR(20), FirstName VARCHAR(20))
INSERT INTO #Users (UserID, LastName, FirstName)
SELECT 1, 'Smith', 'John' UNION ALL
SELECT 2, 'Jones', 'James Earl' UNION ALL
SELECT 3, 'Sahathevarajan', 'Rajkumar' UNION ALL
SELECT 1, 'Smith', 'Alan' UNION ALL
SELECT 5, 'Jones', 'John' UNION ALL
SELECT 5, 'Smith', 'John'
-- Use the result of this...
SELECT UserID, LastName, COUNT(*)
FROM #Users
GROUP BY UserID, LastName
ORDER BY UserID, LastName
-- as the input for this...
SELECT UserID, COUNT(*)
FROM (
SELECT UserID, LastName
FROM #Users
GROUP BY UserID, LastName
) d
GROUP BY UserID
HAVING COUNT(*) > 1
-- which, when joined back to your original table...
SELECT u.*
FROM #Users u
INNER JOIN (
SELECT UserID
FROM (
SELECT UserID, LastName
FROM #Users
GROUP BY UserID, LastName
) d
GROUP BY UserID
HAVING COUNT(*) > 1
) dupes ON dupes.UserID = u.UserID
-- gives your requested result.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 7:28 am
Fantastic! Three different methods in as many minutes!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 11, 2010 at 10:28 am
Thank you so much for the quick and plentiful responses. Chris, I tried your suggested code and it works pretty good except that I also need to exclude from the results the rows where LastName is the same. That is, I want only the records where UserID is the same as another row's UserID, but the LastName is not the same. Your solution includes both rows where LastName is different and rows where LastName is the same. Any ideas?
Thank you!
January 11, 2010 at 12:37 pm
jmcnemar (1/11/2010)
Thank you so much for the quick and plentiful responses. Chris, I tried your suggested code and it works pretty good except that I also need to exclude from the results the rows where LastName is the same. That is, I want only the records where UserID is the same as another row's UserID, but the LastName is not the same. Your solution includes both rows where LastName is different and rows where LastName is the same. Any ideas?Thank you!
No problem - but there's a question for you first (isn't there always). How do you determine which row is the row which is duplicated, and which row(s) are the duplicates of it?
Cheers
ChrisM@home
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 11, 2010 at 12:55 pm
If the UserID and LastName are the same in multiple rows, I do not want to include any of those rows. I only want to include rows where the UserID is the same as in any other row, but with different LastName Value than any other row with the same UserID. Where there some rows with same UserId/same LastName and other rows with that UserID/different LastName, it does not matter which one of the exact duplicates it picks up. For example:
Include these:
UserID LastName
1 Jones
1 Smith
3 Jones
3 Doe
Do not include these:
UserID LastName
1 Jones
1 Jones
3 Doe
3 Doe
Adding a DISTINCT qualifier to your code may have done this trick for me. That is I did not use SELECT * in the outer query as in your example, I did SELECT DISTINCT UserID, LastName...instead
Thank you!
January 12, 2010 at 3:36 am
No problem:
SELECT u.*, dupes.MainLastname
FROM #Users u
INNER JOIN (
SELECT UserID, MIN(LastName) AS MainLastname
FROM (
SELECT UserID, LastName
FROM #Users
GROUP BY UserID, LastName
) d
GROUP BY UserID
HAVING COUNT(*) > 1
) dupes ON dupes.UserID = u.UserID
AND dupes.MainLastname <> u.LastName
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply