Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Query help needed .

    Hi pals,

    I need small help.
    I have a table with data as follows.


    select * from test_data

    c1 c2 c3
    ----------------------
    111 4 101
    122 3 101
    133 2 102
    144 2 103
    155 1 103

    Now i need to generate a new column c4 as sequence number in the output


    I need to get the output something as follows

    c4 c1 c2 c3
    -----------------------------
    1 111 4 101
    2 122 3 101
    1 133 2 102
    1 144 2 103
    2 155 1 103

    The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).

    Can anyone help me out on this regard.

    Thanks in advance,
    franky

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what version sql server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    SQL Server 2005

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select row_number() over(partition by c3 order by c2 desc) as c4
         , c1 , c2 , c3
      from test_table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •