Solved

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

Posted on 2009-04-10
13
315 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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