Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

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

 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;

Open in new window


Results
User generated image
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Can you show the SELECT that goes into #temp?
Avatar of Larry Brister

ASKER

I will be back at my desk in one hour
Here you go

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;

Open in new window

I actually came up with this....
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' )

Open in new window

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;

Open in new window

Sharath
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks... sorry for the late get back