Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2015
    Posts
    10

    Lightbulb Unanswered: How to update duplicate rows with sequence number

    Original Output:
    Product_ID Status Price Seq
    1 OPEN 150
    1 CLOSED 0
    2 OPEN 100
    3 CLOSED 0
    3 OPEN 400
    3 OPEN 700

    Desired Results:
    Product_ID Status Price Seq
    1 OPEN 150 1
    1 CLOSED 0 2
    2 OPEN 100 1
    3 OPEN 700 1
    3 OPEN 400 2
    3 CLOSED 0 3


    With duplicate Product_IDs, how to update column Seq with number,
    such that for each Product_ID Status DESC i.e. OPEN comes first and CLOSED last,
    and Price DESC i.e. highest Price comes first.

    Any recommendations and suggestions would be greatly appreciated.

    Thank you in advance,
    Zach

  2. #2
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Try following SQL. If there are multiple records for the same product--status--price comibination, you need to adjust SQL so that temp1 returns single record for given product--status--price comibination.


    Code:
    merge into ptable 
    using ( 
    select productid , status , price , 
    ROW_NUMBER() OVER ( PARTITION BY productid order by status desc  , price  desc )  AS rn1 
    from ptable ) temp1
    on
    (temp1.productid = ptable.productid and 
     temp1.status = ptable.status and 
     temp1.price = ptable.price)
    when matched then update set ptable.seq = temp1.rn1
    Satya

  3. #3
    Join Date
    Dec 2015
    Posts
    10
    Thank you so very much for your help, Satya. The query worked beautifully . Truly appreciate!

Posting Permissions

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