Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL :  Need help to  generate sequence Numbers

Posted on 2012-09-19
5
Medium Priority
?
520 Views
Last Modified: 2012-09-19
Hi Experts,

I have a table as below

Tab1
ClassID    RowID  SequenceNo
1               1                 0
1               2                 1
1               3                 2
2               1                 0
2                4                 1

when i try to delete a row from the table based on the ClassID and RowID
EX::
Delete from Tab1 where ClassID = 1 AND ROW ID = 2

Now  i need to update the Sequence No's  as well like below  i.e the sequence no's should be regenerated  for that particular class ID.  Below row with class id 1 and rowid 3 has sequence no changed to 1 from 2 .

Tab1
ClassID    RowID  SequenceNo
1               1                 0

1               3                 1
2               1                 0
2                4                 1


How can i  Achieve this .  Any Suggestions.

Thanks
0
Comment
Question by:B_Pati
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38413775
try this ...

Delete from Tab1 where ClassID = 1 AND ROW ID = 2;

update Tab1 set sequenceNo = ROW_NUMBER() OVER (  partition by ClassID  ORDER BY Rowid)
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1500 total points
ID: 38413828
update t
set SequenceNo = newSeq
from (
select ClassID, RowID, SequenceNo, row_number() over (partion by ClassID order by RowID) newSeq from tab1
) t

Open in new window

0
 
LVL 1

Author Comment

by:B_Pati
ID: 38413893
0
 
LVL 1

Author Comment

by:B_Pati
ID: 38413943
I've requested that this question be closed as follows:

Accepted answer: 0 points for B_Pati's comment #a38413893

for the following reason:

Using the above link  and with slight modification  helped me in finding the solution.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 38413930
And why didn't you just try out our suggestions? In particular my SQL is most simple to use - and it fits totally to your request.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

805 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