none
Group by question

    Question

  • Experts,

    We have few values as:-
    CREATE TABLE #Temp
    (
     ADD1 VARCHAR(55),
     ADD2 VARCHAR(55),
     CITY VARCHAR(20),
     STATE VARCHAR(2)
    )

    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 205', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 210', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', 'Ste 104', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', '', 'Lafayette', 'VA' )

    SELECT *
    FROM #Temp
    --GROUP BY ADD1, CITY, STATE

    DROP TABLE #Temp

    Expected o/p:-
    SELECT '103 Farabee Dr N' AS 'ADD1', 'Ste 104' AS 'ADD2', 'Lafayette' AS 'CITY', 'VA' AS 'STATE'
    UNION ALL
    SELECT '103 Farabee Dr N' AS 'ADD1', '' AS 'ADD2', 'Lafayette' AS 'CITY', 'VA' AS 'STATE'

    Thanks

    Kumar


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

    Wednesday, September 25, 2013 9:38 PM

Answers

  • Try -

    CREATE TABLE #Temp
     (
      ADD1 VARCHAR(55),
      ADD2 VARCHAR(55),
      CITY VARCHAR(20),
      STATE VARCHAR(2)
     )
     
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 205', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 210', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', 'Ste 104', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', '', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 100', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 101', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '104 Carolina', 'Ste 200', 'Montario', 'VA' )
    INSERT INTO #Temp VALUES ( '104 Carolina', '', 'Montario', 'VA' )
    
    SELECT *
    FROM #Temp
     
    ;WITH cte AS 
    (
    SELECT ADD1
    	,ADD2
    	,CITY
    	,STATE
    	,MAX(CASE 
    		WHEN ADD2 = '' 
    			THEN 1 
    			ELSE 0 
    		END) 
    	  OVER (PARTITION BY ADD1, CITY, STATE) AS Mx
     FROM #Temp AS a
     )
     SELECT ADD1,ADD2,CITY,STATE
     FROM cte
     WHERE Mx=1
     
    DROP TABLE #Temp
    
    


    Narsimha

    • Marked as answer by _Kumar Wednesday, September 25, 2013 10:18 PM
    Wednesday, September 25, 2013 10:07 PM

All replies

  • CREATE TABLE #Temp
    (
     ADD1 VARCHAR(55),
     ADD2 VARCHAR(55),
     CITY VARCHAR(20),
     STATE VARCHAR(2)
    )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 205', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 210', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', 'Ste 104', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', '', 'Lafayette', 'VA' )
    SELECT * FROM #Temp WHERE STATE='VA'
     
    DROP TABLE #Temp
     
    SELECT '103 Farabee Dr N' AS 'ADD1', 'Ste 104' AS 'ADD2', 'Lafayette' AS 'CITY', 'VA' AS 'STATE'
    UNION ALL
    SELECT '103 Farabee Dr N' AS 'ADD1', '' AS 'ADD2', 'Lafayette' AS 'CITY', 'VA' AS 'STATE'

    Wednesday, September 25, 2013 9:45 PM
  • If my values are something like this now your query won't give me desired output.

    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 205', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 210', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', 'Ste 104', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', '', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 100', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 101', 'Lafayette', 'VA' )

    Still my expected o/p is the same.

    Let me know.

    Thanks


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

    Wednesday, September 25, 2013 9:49 PM
  • SELECT distinct ADD1, CITY, STATE FROM #Temp Group by not required as no aggregate function called
    Wednesday, September 25, 2013 9:54 PM
  • You're query doesn't give me my expected o/p.

    Please double check ETLMike.


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

    Wednesday, September 25, 2013 9:57 PM
  • It does! Line breaks have not worked as replying on iPhone, only use "SELECT distinct ADD1, CITY, STATE FROM #Temp"


    my mistake I misread the expected results
    • Edited by ETLMike Wednesday, September 25, 2013 10:22 PM
    Wednesday, September 25, 2013 10:01 PM
  • I was asking something like this,

    SELECT	a.*
    FROM	#Temp a
    		INNER JOIN
    		(
    			SELECT	*
    			FROM	#Temp
    			GROUP BY ADD1, ADD2, CITY, STATE
    			HAVING	LEN(ADD2) = 0
    		) b
    		ON a.ADD1 = b.ADD1 AND a.CITY = b.CITY AND a.STATE = b.STATE

    Please let me know if I can write a query in more easier way than this.

    Thanks

    Kumar


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

    Wednesday, September 25, 2013 10:02 PM
  • Try -

    CREATE TABLE #Temp
     (
      ADD1 VARCHAR(55),
      ADD2 VARCHAR(55),
      CITY VARCHAR(20),
      STATE VARCHAR(2)
     )
     
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 205', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '57 Bedford St', 'Ste 210', 'Lubbock', 'TX' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', 'Ste 104', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee Dr N', '', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 100', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '103 Farabee', 'Ste 101', 'Lafayette', 'VA' )
    INSERT INTO #Temp VALUES ( '104 Carolina', 'Ste 200', 'Montario', 'VA' )
    INSERT INTO #Temp VALUES ( '104 Carolina', '', 'Montario', 'VA' )
    
    SELECT *
    FROM #Temp
     
    ;WITH cte AS 
    (
    SELECT ADD1
    	,ADD2
    	,CITY
    	,STATE
    	,MAX(CASE 
    		WHEN ADD2 = '' 
    			THEN 1 
    			ELSE 0 
    		END) 
    	  OVER (PARTITION BY ADD1, CITY, STATE) AS Mx
     FROM #Temp AS a
     )
     SELECT ADD1,ADD2,CITY,STATE
     FROM cte
     WHERE Mx=1
     
    DROP TABLE #Temp
    
    


    Narsimha

    • Marked as answer by _Kumar Wednesday, September 25, 2013 10:18 PM
    Wednesday, September 25, 2013 10:07 PM
  • 	Select a.* from #Temp a  INNER JOIN #Temp b 
    		ON a.ADD1 = b.ADD1 AND a.CITY = b.CITY AND a.STATE = b.STATE
    		WHERE b.add2=''

    Wednesday, September 25, 2013 10:48 PM