update with level break number

Hello, how would you do it in SQL

here is my table


 Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'CREATE'
  art1          1          'REJECT'
  art1          1          'CLOSE'
 
 
 I know that first action is always  'CREATE' for any PRod ID, now I want to correct sequence number for products which occur more than once,  so I would want to have something like this:

 
  Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          2          'CREATE'
  art1          2          'APPROVE'
  art1          2          'CLOSE'
 
  art1          3          'CREATE'
  art1          3          'REJECT'
  art1          3          'CLOSE'
RozamundaAsked:
Who is Participating?
 
momi_sabagCommented:
you want something like

select prodId, row_number() over(partition by prodId, action order by prodId,action) seq, action
from your_table
0
 
RozamundaAuthor Commented:
thanks, but how to update all the rows ?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Rozamunda,

Momi's query will properly generate a new SEQ number for all of the rows, but in terms of your data it may not be that simple.

Using just two items from your example:

Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'APPROVE'
  art1          1          'CLOSE'

If the CREATE actions are resequenced, what protocol should be used to assign the correct row with an APPROVE or CLOSE action to the correct CREATE action?  Is there a timestamp or other item in the row that can help to better identify the rows?


Kent
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RozamundaAuthor Commented:
Hi Kent, action is never resequenced, CREATE is always starting the section, CLOSE finishing,
there is also a time stamp (date, time)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hmm....  I'm not sure that my comment was clear, so...

Can it ever happen that for any product ID, you have two OPEN at the same time?  That is, an OPEN occurs for a product, and before that event has a matching CLOSE, another OPEN is issued?

Kent
0
 
RozamundaAuthor Commented:
Kent, ultimately not,  one pair (productid/seqno) should have one open and one close, all in sequence. Right now everything has only one sequence number i want to fix the data, so the above would take place
0
 
momi_sabagCommented:
if you run my query (And adding the timestamp column to the order by part)
do you get the desired results?
if so, we will turn that into an update statement
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Based on what you've said, I think that I'd follow Momi's advise and resequence the "seq" values for all of the OPEN statements, then follow that up with an UPDATE of the other statements to match everything up.


Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.