Larry Brister
asked on
SQL Server set parent recort in select with row number
In my sql below I get a result set of technical "duplicate" records.
What I need is to add a column that show the "Parent" record.
If row = 1 it is IndividualID
If row > I it is Row 1 IndividualID
Results
What I need is to add a column that show the "Parent" record.
If row = 1 it is IndividualID
If row > I it is Row 1 IndividualID
SELECT x.IndividualID ,
x.FullName ,
x.PhoneNumber ,
x.Active ,
x.DateAdded ,
x.rn
FROM x
JOIN ( SELECT FullName ,
COUNT(FullName) cnt
FROM #temp
GROUP BY FullName
) t ON t.FullName = x.FullName
WHERE t.cnt > 1
ORDER BY FullName ,
rn;
Results
Can you show the SELECT that goes into #temp?
ASKER
I will be back at my desk in one hour
ASKER
Here you go
Full code set
Full code set
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
SELECT b.BusinessID ,
b.BusinessName ,
o.OriginID ,
o.OriginName ,
s.SalesSiteID ,
s.Name SalesSIteName ,
i.IndividualID ,
RTRIM(LTRIM(i.FirstName)) + RTRIM(LTRIM(i.LastName)) AS FullName,
p.PhoneNumber ,
i.Active ,
i.DateAdded
INTO #temp
FROM Individuals i
JOIN dbo.IndividualOrigins oi ON oi.IndividualID = i.IndividualID
JOIN Origins o ON o.OriginID = oi.OriginID
JOIN SalesSites s ON s.SalesSiteID = o.SalesSiteID
JOIN ewBusinesses b ON b.BusinessID = s.BusinessID
LEFT JOIN dbo.IndividualEmails e ON e.IndividualID = oi.IndividualID
JOIN dbo.IndividualPhoneNumbers p ON p.IndividualID = i.IndividualID
WHERE b.BusinessName LIKE '%Nightingale%'
ORDER BY i.LastName ,
i.FirstName ,
i.DateAdded DESC;
WITH x AS ( SELECT IndividualID ,
FullName ,
Active ,
DateAdded ,
rn = ROW_NUMBER() OVER ( PARTITION BY FullName ORDER BY DateAdded DESC )
FROM #temp
)
SELECT x.IndividualID ,
x.DateAdded ,
x.rn
FROM x
JOIN ( SELECT FullName ,
COUNT(FullName) cnt
FROM #temp
GROUP BY FullName
) t ON t.FullName = x.FullName
WHERE t.cnt > 1
ORDER BY t.FullName ,
rn;
ASKER
I actually came up with this....
Unless someone can come up with something better.
Unless someone can come up with something better.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
SELECT b.BusinessID ,
b.BusinessName ,
o.OriginID ,
o.OriginName ,
s.SalesSiteID ,
s.Name SalesSIteName ,
i.IndividualID ,
RTRIM(LTRIM(i.FirstName)) + RTRIM(LTRIM(i.LastName)) AS FullName,
p.PhoneNumber ,
i.Active ,
i.DateAdded
INTO #temp
FROM Individuals i
JOIN dbo.IndividualOrigins oi ON oi.IndividualID = i.IndividualID
JOIN Origins o ON o.OriginID = oi.OriginID
JOIN SalesSites s ON s.SalesSiteID = o.SalesSiteID
JOIN ewBusinesses b ON b.BusinessID = s.BusinessID
LEFT JOIN dbo.IndividualEmails e ON e.IndividualID = oi.IndividualID
JOIN dbo.IndividualPhoneNumbers p ON p.IndividualID = i.IndividualID
WHERE b.BusinessName LIKE '%Nightingale%'
AND i.Active = 1
--AND LastName = 'GYDE' AND FirstName = 'LISA'
ORDER BY i.LastName ,
i.FirstName ,
i.DateAdded ;
WITH x AS ( SELECT IndividualID ,
SalesSIteID,
FullName ,
Active ,
DateAdded ,
rn = ROW_NUMBER() OVER ( PARTITION BY FullName ORDER BY DateAdded ASC )
FROM #temp
)
SELECT x.IndividualID ,
x.SalesSIteID,
x.FullName,
x.DateAdded ,
x.rn
INTO #Results
FROM x
JOIN ( SELECT FullName ,
COUNT(FullName) cnt
FROM #temp
GROUP BY FullName
) t ON t.FullName = x.FullName
WHERE t.cnt > 1
ORDER BY t.FullName ,
rn;
SELECT t.IndividualID ,
t.SalesSiteID ,
t.FullName ,
t.DateAdded ,
t.rn ,
r.IndividualID UpdateToIndividualID ,
r.SalesSiteID UpdateToSalesSiteID
FROM #Results t
JOIN ( SELECT *
FROM #Results
WHERE rn = 1
) r ON r.FullName = t.FullName
WHERE t.FullName NOT IN ( 'NONAME' )
check this.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
SELECT b.BusinessID ,
b.BusinessName ,
o.OriginID ,
o.OriginName ,
s.SalesSiteID ,
s.Name SalesSIteName ,
i.IndividualID ,
RTRIM(LTRIM(i.FirstName)) + RTRIM(LTRIM(i.LastName)) AS FullName,
p.PhoneNumber ,
i.Active ,
i.DateAdded
INTO #temp
FROM Individuals i
JOIN dbo.IndividualOrigins oi ON oi.IndividualID = i.IndividualID
JOIN Origins o ON o.OriginID = oi.OriginID
JOIN SalesSites s ON s.SalesSiteID = o.SalesSiteID
JOIN ewBusinesses b ON b.BusinessID = s.BusinessID
LEFT JOIN dbo.IndividualEmails e ON e.IndividualID = oi.IndividualID
JOIN dbo.IndividualPhoneNumbers p ON p.IndividualID = i.IndividualID
WHERE b.BusinessName LIKE '%Nightingale%'
AND i.Active = 1
--AND LastName = 'GYDE' AND FirstName = 'LISA'
ORDER BY i.LastName ,
i.FirstName ,
i.DateAdded ;
WITH x AS ( SELECT IndividualID ,
SalesSIteID,
FullName ,
Active ,
DateAdded ,
rn = ROW_NUMBER() OVER ( PARTITION BY FullName ORDER BY DateAdded ASC ),
cnt = COUNT(*) OVER (PARTITION BY FullName)
FROM #temp
)
SELECT x.IndividualID ,
x.SalesSIteID,
x.FullName,
x.DateAdded ,
x.rn
INTO #Results
FROM x
WHERE cnt > 1
ORDER BY FullName ,
rn;
SELECT t.IndividualID ,
t.SalesSiteID ,
t.FullName ,
t.DateAdded ,
t.rn ,
r.IndividualID UpdateToIndividualID ,
r.SalesSiteID UpdateToSalesSiteID
FROM #Results t
WHERE t.FullName NOT IN ( 'NONAME' )
AND rn = 1;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks... sorry for the late get back