asked on

Update query for inserting row_count group-based

I have an invoice "line" file. The simplified structure is:

InvoiceNumber (varchar)
LineNumber (int)
Item (varchar)

Task: For each InvoiceNumber I need to insert a row_count into LineNumber. But I need it grouped by the InvoiceNumber.

So, if InvoiceNumber A1000 had fifty records, the row_count would start at 1 and go to 50. For the next InvoiceNumber, the row_count would start at 1 again. Thanks much. Please let me know if this isn't clear.

(I can do this in either SQL 2005 or Access 2003)

The data should look like this:
A1000  1  Hammer
A1000  2  Screwdriver
A1000  3  Boat
C2500  1  Book
C2500  2  Car
E9999  1  House

Open in new window

JestersGrind

In SQL 2005 you can use the ROW_NUMBER function. Something like this:

SELECT InvoiceNumber, ROW_NUMBER () OVER (ORDER BY InvoiceNumber, LineNumber, Item) AS LineNumber, Item
FROM Table1

Greg

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nathan Riley

Try this:

update table
set linenumber = row_number () over (order by InvoiceNumber, Item)
from table

Open in new window

SOLUTION

dan_neal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dan_neal

You must use partition by to get the line number to restart at 1 for each invoiceno.

coffeeshop

You can use a function like this:

Public Function RowNr(ByVal vRandomQueryField As Variant) As Integer

Static vRandomQueryFieldSave As Variant
Static iRowNr As Integer

If (vRandomQueryFieldSave <> vRandomQueryField) Then
'Reset Counter
iRowNr = 0
vRandomQueryFieldSave = vRandomQueryField
Else
'Count#
iRowNr = iRowNr + 1
End If

'Return #
RowNr = iRowNr

End Function

Call it from a query like:

SELECT InvoiceNumber, RowNr([InvoiceNumber]) AS RowCount
FROM tblX;

cs2data

ASKER

I split it because you both gave most of the answer: The PARTITION BY is required for this but you must also use ORDER BY as well. Thanks for the help.