Solved

On Insert Sequence Line Number in SQL One Line at a Time

Posted on 2009-04-10
13
309 Views
Last Modified: 2012-05-06
I need a function that will give me the ability to sequence line numbers within a record set when records are inserted and or deleted.

I have two tables a header table and a detailed table.  Within the detailed table I have the standard indexed ID field and a second ID field named AAID.  The AAID captures the ID of the header row through a session id assigned on the insert of the first record.

The detailed records that match the header id can only have a maximum of 50 Line Numbers inserted.  So for each record the AAID is unique and which indicated a new sequencing of records.

Also if a user deletes a record, say number 5, then we need to re-sequence.

I hope that this is clear and not to fuzzy.

Let me know.
Thanks
0
Comment
Question by:gagnonmv
  • 6
  • 6
13 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24118824
When you say "Also if a user deletes a record, say number 5, then we need to re-sequence.", are you talking about the Header Row or the Detail Row?

Are you will/able to use a stored procedure to do your requesence step?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24119056
I think that you can probably leverage the row_number() function.

The syntax would be:

row_number() over (partition by PK_FROM_HEADER_RECORD order by WHATTOORDERBY)
0
 

Author Comment

by:gagnonmv
ID: 24119138
With the sequencing we are only talking about the detailed table, not the header
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24119208
If there is something other than the AAID that lets you order the Detail rows, Brandon's approach could work nicely.  If, on the other hand, there is not something other than the AAID that lets you order the rows properly, then you will have to do some manipulating to get things to work right.
Have you considered adding an Identity column in the Details table so that you can maintain the order based on that and then displaying the AAID based on something like Brandon's approach.  You could still maintain the AAID and resequence it (if that is a requirement of some sort) but, by having the Identity column, you would be able to run a query that updates the AAID column from the ROWNUMBER() by selecting the data ordered by the ID and Identity columns.
0
 

Author Comment

by:gagnonmv
ID: 24119379
I do have an I'd field inaddtion to the AAID column.                                              
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24119777
In that case, use the ROWNUMBER approach, selecting the data for the given ID from the Header row and the identity column to maintain the order of the rows within the Details.
I would start by setting up a SELECT query that pulls the Header ID, the Detail Identity column, the current AAID, and the ROWNUMBER calculation for the NewAAID.  That will let you establish that you are getting a good calculation of the new AAID.  THen you can use that to feed the UPDATE query so that you can join on the Header ID and the Detail Identity column and UPDATE the AAID column to the ROWNUMBER.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:gagnonmv
ID: 24130035
So this is the Select Query with the Row Number which works fine.

SELECT ID, AAID, ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID) AS 'Row_Number'
FROM [ABC].[dbo].[AA_DETAIL]

So how do I write the update query to incorporate this code.

Thanks
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130098
It is fairly simple in SS2005.  
If the query you provided gives you a good line new number with which to update the AAID, then try the following:

UPDATE T

SET    AAID = RowNumber

FROM   AA_DETAIL T

INNER JOIN

(

SELECT ID, 

       AAID, 

       ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID)

       AS 'Row_Number' 

FROM [ABC].[dbo].[AA_DETAIL]

) Z

ON T.ID = Z.ID

Open in new window

0
 

Author Comment

by:gagnonmv
ID: 24130231
What goes in the place of T and Z, I assume Table 1 and table 2? and I keep getting an incorrect sytax on line 11 ')'

Thanks
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24130308
T and Z are aliases and NOTHING should be used to replace them.  In other words, you should not have to modify that code at all.  If you put the table names in those places, that would explain the error on line 11.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 500 total points
ID: 24130313
You may want to get a couple of books on T-SQL and study them. ;-)  I can highly recommend "The Guru's Guide to Transact-SQL" and "SSQL Cookbook".
0
 

Accepted Solution

by:
gagnonmv earned 0 total points
ID: 24130568
So here is the code, but it doesn't update the records:

UPDATE T
SET    AAID = RowNumber
FROM   [ABC].[dbo].[AA_DETAIL] T
INNER JOIN
(
SELECT ID,
       AAID,
       ROW_NUMBER() OVER(PARTITION BY AAID ORDER BY ID)
       AS 'RowNumber'
FROM [ABC].[dbo].[AA_DETAIL]
) Z
ON T.ID = Z.ID
0
 

Author Comment

by:gagnonmv
ID: 24130912
Sorry my mistake, I have gotten this to work correctly.  Thanks for the help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now