Deriving an Incrementing Order Line Number

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)

Deriving an Incrementing Order Line Number

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-28 : 08:41:44

Curt writes "I’m extracting order information that can contain multiple lines for the same order (in other words if three things are ordered there are three items). The kicker is that the table I’m extracting from does not contain an order line number but the table that I need to populate with this information requires such a field. For each unique order the order line number column must begin with one and then increment by one for each order. For instance if I extracted the following

Order:
A
A
A
B
B

The resulting table needs to be populated with

Order	Line #
A	1
A	2
A	3
B	1
B	2

The only way I can think of deriving the line number is through the use of a couple of temporary table and a cursor and since I normally avoid cursors like the plague I was hoping you might be able to come up with a solution.

Thanks for any help you can provide. By the way I'm working with SQL Server 2000.

Curt"

sica
Posting Yak Master

143 Posts

Posted - 2002-01-28 : 10:08:46

Check this out:

CREATE TABLE #TEMP (ID INT, WORD VARCHAR(2) NULL)

INSERT INTO #TEMP VALUES (20, 'A')
INSERT INTO #TEMP VALUES (21, 'A')
INSERT INTO #TEMP VALUES (22, 'A')
INSERT INTO #TEMP VALUES (23, 'B')
INSERT INTO #TEMP VALUES (24, 'B')
INSERT INTO #TEMP VALUES (25, 'B')
INSERT INTO #TEMP VALUES (26, 'C')

SELECT ID,
WORD,
( SELECT COUNT(*)+1 FROM #TEMP T1 WHERE T1.ID < #TEMP.ID ),
( SELECT COUNT(*)+1 FROM #TEMP T2 WHERE T2.ID < #TEMP.ID AND T2.WORD = #TEMP.WORD)
FROM #TEMP

Sica

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-01-28 : 11:47:25

How about creating an identity field for the line items table, or if its a denormalized invoice table, grab the identity field from the source lineitem table. If you have neither probably creating a line items table would be quite helpful.

Creating a PK on the temp table (ID, word) would speed up the above remedy considerabley as the subquery in the select statement will most definitly be processed as a non-cached/non-spooled nested loop join. I think the ID column in the temp table should be an identity field also. Less code, faster.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-28 : 16:05:42

quote:

Check this out:

CREATE TABLE #TEMP (ID INT, WORD VARCHAR(2) NULL)

INSERT INTO #TEMP VALUES (20, 'A')
INSERT INTO #TEMP VALUES (21, 'A')
INSERT INTO #TEMP VALUES (22, 'A')
INSERT INTO #TEMP VALUES (23, 'B')
INSERT INTO #TEMP VALUES (24, 'B')
INSERT INTO #TEMP VALUES (25, 'B')
INSERT INTO #TEMP VALUES (26, 'C')

SELECT ID,
WORD,
( SELECT COUNT(*)+1 FROM #TEMP T1 WHERE T1.ID < #TEMP.ID ),
( SELECT COUNT(*)+1 FROM #TEMP T2 WHERE T2.ID < #TEMP.ID AND T2.WORD = #TEMP.WORD)
FROM #TEMP

Sica

This will likely perform a little better, depending on indexes and number of rows:

SELECT A.ID,
A.WORD,
Count(*),
SUM( CASE WHEN A.Word = B.Word THEN 1 ELSE 0 END)
FROM #TEMP A
JOIN #Temp B ON B.ID <= A.ID
GROUP BY A.ID,
A.WORD

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-28 : 16:06:37

quote:

Check this out:

CREATE TABLE #TEMP (ID INT, WORD VARCHAR(2) NULL)

INSERT INTO #TEMP VALUES (20, 'A')
INSERT INTO #TEMP VALUES (21, 'A')
INSERT INTO #TEMP VALUES (22, 'A')
INSERT INTO #TEMP VALUES (23, 'B')
INSERT INTO #TEMP VALUES (24, 'B')
INSERT INTO #TEMP VALUES (25, 'B')
INSERT INTO #TEMP VALUES (26, 'C')

SELECT ID,
WORD,
( SELECT COUNT(*)+1 FROM #TEMP T1 WHERE T1.ID < #TEMP.ID ),
( SELECT COUNT(*)+1 FROM #TEMP T2 WHERE T2.ID < #TEMP.ID AND T2.WORD = #TEMP.WORD)
FROM #TEMP

Sica

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources