Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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

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
gagnonmv
Asked:
gagnonmv
  • 6
  • 6
2 Solutions
 
8080_DiverCommented:
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
 
BrandonGalderisiCommented:
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
 
gagnonmvAuthor Commented:
With the sequencing we are only talking about the detailed table, not the header
0
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.

 
8080_DiverCommented:
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
 
gagnonmvAuthor Commented:
I do have an I'd field inaddtion to the AAID column.                                              
0
 
8080_DiverCommented:
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
 
gagnonmvAuthor Commented:
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
 
8080_DiverCommented:
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
 
gagnonmvAuthor Commented:
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
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
gagnonmvAuthor Commented:
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
 
gagnonmvAuthor Commented:
Sorry my mistake, I have gotten this to work correctly.  Thanks for the help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now