locked
SQL Server RRS feed

  • Question

  • I have a table like this

    C1,C2,C3
    --------
    a,b,c
    x,y,z
    p,q,r
    a,b,c
    a,b,c
    x,y,z

    Where records a,b,c and x,y,z are repeated twice. but record p,q,r is having only one entry. So I have to interduce one more column to this table so that final table will be something like this.

    C1,C2,C3,C4
    --------
    a,b,c,1
    x,y,z,1
    p,q,r,1
    a,b,c,0
    a,b,c,0
    x,y,z,0

    For every record, for its first accurance I need to set the column4 as 1, if that perticular record is repeated, then I need to insert column4 as 0.

    How can I achieve this using SQL Query..

    Please help me to solve this.

     

    Thanks in advance

    ---------------

     

     

    Thursday, February 2, 2012 1:43 PM

Answers

  • Hi Rajesh

     

    Not sure if this is the right newsgroup..

     

    Try this :

     

    CREATE TABLE #T1 (C1 varchar(1),C2 varchar(1),C3 varchar(1),C4 int)

     

    INSERT INTO #T1 (C1,C2,C3)

    VALUES ( 'a','b','c'), ('x','y','z'), ('p','q','r'), ('a','b','c'), ('a','b','c'),

     ('x','y','z') 

     

     -- Here is the trick

     ;WITH CTE  as

    (Select     seq = ROW_NUMBER() OVER (PARTITION BY C1,C2,C3 ORDER BY C1)

                , C1, C2, C3,C4

          from #T1

        

    )

    UPDATE CTE

      SET C4 = 1

     WHERE seq = 1

     

     

    select * from #T1

    DROP TABLE #T1

     

    Javier Villegas / @javier_vill / http://sql-javier-villegas.blogspot.com/

    • Marked as answer by Rajesh S Hegde Friday, February 3, 2012 11:31 AM
    Thursday, February 2, 2012 4:50 PM

All replies

  • Hi Rajesh

     

    Not sure if this is the right newsgroup..

     

    Try this :

     

    CREATE TABLE #T1 (C1 varchar(1),C2 varchar(1),C3 varchar(1),C4 int)

     

    INSERT INTO #T1 (C1,C2,C3)

    VALUES ( 'a','b','c'), ('x','y','z'), ('p','q','r'), ('a','b','c'), ('a','b','c'),

     ('x','y','z') 

     

     -- Here is the trick

     ;WITH CTE  as

    (Select     seq = ROW_NUMBER() OVER (PARTITION BY C1,C2,C3 ORDER BY C1)

                , C1, C2, C3,C4

          from #T1

        

    )

    UPDATE CTE

      SET C4 = 1

     WHERE seq = 1

     

     

    select * from #T1

    DROP TABLE #T1

     

    Javier Villegas / @javier_vill / http://sql-javier-villegas.blogspot.com/

    • Marked as answer by Rajesh S Hegde Friday, February 3, 2012 11:31 AM
    Thursday, February 2, 2012 4:50 PM
  • Hi
    Javier,
                 Thank you very much.. Its working exactly in the way that I want.. I was not having the clear idea of Over Clause. Thanks a lot...
    Friday, February 3, 2012 11:32 AM