none
Re : Sql Statement to find Same value in rows of single column RRS feed

  • Question

  • How to find if all the rows of a column have same value

    Field1    Field2

    A               1

    B               1

    C               1

    D               1

    E               2

    F                1

    if the any of the row have value a different value then replace entire row with 0

    thanx in advance

    biju

    Thursday, May 24, 2012 9:55 AM

Answers

  • Select Field1
    , Case 
    	When (SELECT count(distinct field2) FROM Your_Table)> 0 Then 0 Else field2 End as Field2
    From Your_Table

    • Marked as answer by Biju2 Thursday, May 24, 2012 10:31 AM
    Thursday, May 24, 2012 10:24 AM

All replies

  • Hi,

    can you post how u need  your output:


    PS.Shakeer Hussain

    Thursday, May 24, 2012 9:59 AM
  • The result should be

    if any of the value in Field2 is different then replace with 0

    like

    Field1    Field2

    A               0

    B               0

    C               0

    D               0

    E               0

    F                0

    Thanx
    Thursday, May 24, 2012 10:01 AM
  • Hi,

    Is there any Primary key available in that Table


    PS.Shakeer Hussain

    Thursday, May 24, 2012 10:05 AM
  • Hi, 

    You can do something like,

    Update temp Set Field2 = case when Id > 0 Then 0 Else  Field2 End


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

    Thursday, May 24, 2012 10:06 AM
  • No primary key

    but i need sql query only. I dont want to update table

    thanx

    Thursday, May 24, 2012 10:09 AM
  • not to update table only sql query ..

    Thursday, May 24, 2012 10:11 AM
  • SELEC T filed1,Field2=CASE WHEN EXIST(Select * from tablename where filed1 <>filed2) THEN 0 END FROM TAbleName


    PS.Shakeer Hussain


    Thursday, May 24, 2012 10:17 AM
  • Sorry Mr. Syed

    But I need if there is any change in value in Field2

    if the table has records like

    Field1    Field2

    A               1

    B               1

    C               1

    D               1

    E               1

    F                1

    then it is fine

    or if

    Field1    Field2

    A               100

    B               100

    C               100

    D               100

    E               100

    F                100

    this is also fine

    but when the table has record like

    Field1    Field2

    A               100

    B               99

    C               100

    D               100

    E               100

    F                100

    then the field2 column should show value 0

    Thanx

    Biju

    Thursday, May 24, 2012 10:22 AM
  • Hello,

    for me , it is a strange requirement. 

    however try this code(for less column this is okay, but if it is large then you should consider something else)

    SELECT  
    case when
    Field1  = Field2 then 0
    else Field1 end as Field1 , case when
    Field1  = Field2 then 0
    else Field2 end as Field2
    from [YourTable]

    Regards
    Satheesh
    Thursday, May 24, 2012 10:23 AM
  • Select Field1
    , Case 
    	When (SELECT count(distinct field2) FROM Your_Table)> 0 Then 0 Else field2 End as Field2
    From Your_Table

    • Marked as answer by Biju2 Thursday, May 24, 2012 10:31 AM
    Thursday, May 24, 2012 10:24 AM
  • Thanx Mr. Wang

    gr8

    Thursday, May 24, 2012 10:31 AM
  • ;with cteTest as (select max(Field2) as MaxFld, Min(Field2) as MinFld from myTable)
    select Field1, case when MaxFld = MinFld  then Field2 else 0 end as Field2
    from myTable, cteTest

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, May 24, 2012 1:14 PM
    Moderator