[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update with level break number

Posted on 2012-08-12
8
Medium Priority
?
604 Views
Last Modified: 2012-08-14
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'
0
Comment
Question by:Rozamunda
  • 3
  • 3
  • 2
8 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 38286056
you want something like

select prodId, row_number() over(partition by prodId, action order by prodId,action) seq, action
from your_table
0
 

Author Comment

by:Rozamunda
ID: 38286234
thanks, but how to update all the rows ?
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38287101
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Rozamunda
ID: 38287291
Hi Kent, action is never resequenced, CREATE is always starting the section, CLOSE finishing,
there is also a time stamp (date, time)
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38287378
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
 

Author Comment

by:Rozamunda
ID: 38287632
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38287646
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38287658
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question