locked
SQL SERVER COMPARE SAME COLUMN RRS feed

  • Question

  • Hello all,

    I face a little problem in my sql query.I want o compare data in same table and make a simple output table.

    I have a table A here have column 1,column 2,column 3,column 4,column 5.

    column 5 is flag column its flag data "U" and "D". i need to find those all value which have "U" and "D" flag with all other column data same .Mean all other column value same but comuln5 value different.

    Sorry for my english .

    if need more details please ask me.

    Thanks for help

    Muzahid

    Monday, March 10, 2014 7:47 AM

Answers

  • Not sure, I understood your question...

    But Try the below

    Create table tablename (col1 int ,Col2 int, Col3 int,Col4 int,Col5 char(1))
    
    Insert into tablename values(1,2,3,4,'U'),(1,2,3,4,'D'),(2,3,4,5,'U'),(2,3,4,5,'I'),(3,4,5,6,'U')
    
    ;With cte
    as(
    Select *,COUNT(Case when Col5 in('U','D') then 1 else NULL end)Over(partition by Col1,Col2,Col3,Col4 )  Cnt 
    From tablename)
    Select Col1,Col2,Col3,Col4,Col5 From cte where Cnt>1
    
    Drop table tablename
    
    

    • Proposed as answer by SathyanarrayananS Monday, March 10, 2014 9:18 AM
    • Marked as answer by Fanny Liu Tuesday, March 18, 2014 11:27 AM
    Monday, March 10, 2014 8:03 AM

All replies

  • Not sure, I understood your question...

    But Try the below

    Create table tablename (col1 int ,Col2 int, Col3 int,Col4 int,Col5 char(1))
    
    Insert into tablename values(1,2,3,4,'U'),(1,2,3,4,'D'),(2,3,4,5,'U'),(2,3,4,5,'I'),(3,4,5,6,'U')
    
    ;With cte
    as(
    Select *,COUNT(Case when Col5 in('U','D') then 1 else NULL end)Over(partition by Col1,Col2,Col3,Col4 )  Cnt 
    From tablename)
    Select Col1,Col2,Col3,Col4,Col5 From cte where Cnt>1
    
    Drop table tablename
    
    

    • Proposed as answer by SathyanarrayananS Monday, March 10, 2014 9:18 AM
    • Marked as answer by Fanny Liu Tuesday, March 18, 2014 11:27 AM
    Monday, March 10, 2014 8:03 AM
  • Please post sample data + desired result. Always state what version you are using...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 10, 2014 8:03 AM
    Answerer
  • Hi Muzahid,

    I hope this is what you meant:

    SELECT
    	COL1 ,
    	COL2 ,
    	COL3 ,
    	COL4
    FROM
    	TableName
    WHERE
    	COL5 IN ('U' , 'D')
    GROUP BY
    	COL1 ,
    	COL2 ,
    	COL3 ,
    	COL4
    HAVING
    	COUNT (DISTINCT COL5) = 2;
    

    Good luck!

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeira.co.il

    Monday, March 10, 2014 8:15 AM
  • Not sure, I understood your question...

    But Try the below

    Create table tablename (col1 int ,Col2 int, Col3 int,Col4 int,Col5 char(1))
    
    Insert into tablename values(1,2,3,4,'U'),(1,2,3,4,'D'),(2,3,4,5,'U'),(2,3,4,5,'I'),(3,4,5,6,'U')
    
    ;With cte
    as(
    Select *,COUNT(Case when Col5 in('U','D') then 1 else NULL end)Over(partition by Col1,Col2,Col3,Col4 )  Cnt 
    From tablename)
    Select Col1,Col2,Col3,Col4,Col5 From cte where Cnt>1
    
    Drop table tablename
    


    Thanks Latheesh NK  Its work on my table. thanks u so much
    Monday, March 10, 2014 8:25 AM
  • Thanks all Latheesh NK  help work on my table .

    Thanks again for quick help .

    Muzahid

    Monday, March 10, 2014 8:27 AM
  • Without CTE also you could achieve this. Just FYI.

    SELECT T1.*
    FROM tablename T1 JOIN tablename T2 ON T1.col1=T2.col1 AND T1.col2=T2.col2 AND T1.col3=T2.col3 AND T1.col4=T2.col4 AND T1.col5<>T2.col5 AND T1.Col5 IN ('U','D')
    AND T2.Col5 IN ('U','D')
    


    Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

    Monday, March 10, 2014 11:33 AM