Identity type column solution? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity type column solution?

Author

Topic

coultard1
Starting Member

4 Posts

Posted - 2004-10-12 : 20:51:22

I am trying to creating line numbering in a table, which someone has put me on identity as the way to have auto numbering.

From what I have read about about identity is that if you have 100 records in a table then the identity field will go from 1 to 100 etc.

The solution I am looking for is the line number column to reset back to one for each different order in the table. So I may have 5 orders in the table which each contain 10 lines, and need to distinguish between the orders and still have line numbering for them.

Is there a addition to the identity type that can handle this (resetting every time invoice number changes?) or this is done by another way? if so how?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-12 : 22:17:30

You can't get an identity column to work that way, and creating insert or trigger logic to number the lines is not easy and certainly not worth the hassle. Since the line numbers are essentially a presentation issue, you can use a regular identity column in the line items table, and create a stored procedure to display them like so:

CREATE PROCEDURE ShowLineItems AS
SET NOCOUNT ON
CREATE TABLE #lines (InvoiceID int not null, ID int not null, LineNo int not null DEFAULT(0), CONSTRAINT PK_#lines PRIMARY KEY CLUSTERED(InvoiceID, ID))
INSERT INTO #lines(InvoiceID, ID) SELECT InvoiceID, ID FROM LineItems
DECLARE @lineno int, @invoiceID int
SELECT @lineno=1, @invoiceID=-1
UPDATE #lines SET @lineno=LineNo=CASE WHEN InvoiceID=@invoiceID THEN @lineno+1 ELSE 1 END, @invoice=InvoiceID
SELECT InvoiceID, LineNo FROM #lines ORDER BY InvoiceID, LineNo --the ORDER BY is optional, but should be included for completeness
DROP TABLE #lines

You'll either want to add the additional columns you want to display to the #lines table, or you can join it to the LineItems table for presentation purposes. The trick used is documented here:

http://www.sqlteam.com/item.asp?ItemID=765

It depends on a clustered index to work properly though, that's why I added the primary key the way I did. Keep in mind that this is non-standard behavior and may not work properly in future SQL Server releases.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 22:52:28

why couldn't you do the ever popular


Select 
OrderId,
ItemId,
LineItem = (Select count(*) From myTable Where orderId = A.OrderId and ItemId <= A.itemId)
From myTable A
Order By 1, 2

Corey

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-12 : 23:33:27

You can, but that query can end up with some nasty loop joins on a big table. Plus, if you had two rows with the same ItemID, you'd get a skip in the line number sequence, and/or a dupe line number. Yeah, it shouldn't happen, but...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 23:40:53

oh well... thats true.

I guess I usually manage to avoid to many special cases that break it...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources