Solved

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

Posted on 2009-04-10
13
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query with cast 38 64
Help Required 2 56
Replication failure 1 31
Parsing this XML works but the other one doesn't 9 35
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…

732 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