none
Dups Query

    Question

  • Experts,

    CREATE TABLE TEMP
    (
    	P_ID	INT,
    	IND_ID	INT,
    	Add1	VARCHAR(30),
    	City	VARCHAR(20),
    	State	VARCHAR(2)
    )
    
    INSERT INTO TEMP VALUES ( 100, 1 , '360 New Albany Plz', 'Louisville', 'NC' )
    INSERT INTO TEMP VALUES ( 101, 1 , '360 New Albany Plz', 'Louisville', 'NC' )
    INSERT INTO TEMP VALUES ( 102, 1 , '11 E Lewis and Pkwy', 'Anderson', 'KY' )
    
    INSERT INTO TEMP VALUES ( 103, 2 , '1400 Walter Reed Rd', 'Indianapolis', 'IN' )
    INSERT INTO TEMP VALUES ( 103, 2 , '247 Biltmore Ave', 'Chicago', 'IL' )
    INSERT INTO TEMP VALUES ( 104, 2 , '1400 Walter Reed Rd', 'Indianapolis', 'IN' )
    INSERT INTO TEMP VALUES ( 105, 2 , '4420 Dixie Hwy', 'Altanta', 'GA' )
    
    SELECT	*--IND_ID
    FROM	TEMP
    --GROUP BY	IND_ID, Add1, City, State
    --HAVING		COUNT(*) > 1
    
    DROP TABLE TEMP
    

    Expected o/p:-

    SELECT	'100' AS 'P_ID', '1' AS 'IND_ID', '360 New Albany Plz' AS 'Add1', 'Louisville' AS 'City', 'NC' AS 'State'
    UNION ALL
    SELECT	'101' AS 'P_ID', '1' AS 'IND_ID', '360 New Albany Plz' AS 'Add1', 'Louisville' AS 'City', 'NC' AS 'State'
    UNION ALL
    SELECT	'103' AS 'P_ID', '2' AS 'IND_ID', '1400 Walter Reed Rd' AS 'Add1', 'Indianapolis' AS 'City', 'IN' AS 'State'
    UNION ALL
    SELECT	'104' AS 'P_ID', '2' AS 'IND_ID', '1400 Walter Reed Rd' AS 'Add1', 'Indianapolis' AS 'City', 'IN' AS 'State'
    

    Thanks in advance

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS

    Monday, August 05, 2013 8:25 PM

Answers

  • Is this is the correct way to do it?

    ;WITH T AS
    (
    	SELECT	*, DENSE_RANK() OVER (PARTITION BY IND_ID ORDER BY Add1 + City + State) AS Row
    	FROM	TEMP
    )
    
    ,set0 AS
    (
    	SELECT	IND_ID, Row
    	FROM	T
    	GROUP BY IND_ID, Row
    	HAVING	COUNT(*) > 1
    )
    
    --SELECT	*
    --FROM	set0
    
    SELECT	a.*, b.*
    FROM	T a INNER JOIN set0 b ON a.Row = b.Row AND a.IND_ID = b.IND_ID

    Please help me with more simpler query.

    Thanks


    Please do let us know your feedback. Thank You - KG, MCTS

    • Marked as answer by _Kumar Saturday, August 10, 2013 2:43 AM
    Monday, August 05, 2013 8:58 PM

All replies

  • Is this is the correct way to do it?

    ;WITH T AS
    (
    	SELECT	*, DENSE_RANK() OVER (PARTITION BY IND_ID ORDER BY Add1 + City + State) AS Row
    	FROM	TEMP
    )
    
    ,set0 AS
    (
    	SELECT	IND_ID, Row
    	FROM	T
    	GROUP BY IND_ID, Row
    	HAVING	COUNT(*) > 1
    )
    
    --SELECT	*
    --FROM	set0
    
    SELECT	a.*, b.*
    FROM	T a INNER JOIN set0 b ON a.Row = b.Row AND a.IND_ID = b.IND_ID

    Please help me with more simpler query.

    Thanks


    Please do let us know your feedback. Thank You - KG, MCTS

    • Marked as answer by _Kumar Saturday, August 10, 2013 2:43 AM
    Monday, August 05, 2013 8:58 PM
  • Hi Kumar,

    It's a little bit clunky, but this should work for you.

    WITH DUP AS(
    SELECT	*, ROW_NUMBER() OVER(PARTITION BY IND_ID, ADD1, CITY, STATE ORDER BY P_ID DESC) ROW_NUM
    FROM	TEMP
    )
    ,DUP2 AS(
    SELECT	*, ROW_NUMBER() OVER(PARTITION BY IND_ID, ADD1, CITY, STATE ORDER BY P_ID ASC) ROW_NUM
    FROM	TEMP
    )
    
    SELECT P_ID, IND_ID, ADD1, CITY, STATE
    FROM DUP
    WHERE ROW_NUM > 1
    
    UNION 
    
    SELECT P_ID, IND_ID, ADD1, CITY, STATE
    FROM DUP2
    WHERE ROW_NUM > 1


    Jordan Johnson

    Monday, August 05, 2013 9:00 PM