[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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

2 Solutions
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



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


Nathan RileyFounder/CTOCommented:
Try this:
update table
set linenumber = row_number () over (order by InvoiceNumber, Item)
from table

Open in new window

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Try this query in SQL 2005:
Make sure to correct the table name.

SELECT invoiceno, 
    ROW_NUMBER() OVER (PARTITION BY invoiceno) AS line number, item
FROM <table_name>

Open in new window

You must use partition by to get the line number to restart at 1 for each invoiceno.
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
    iRowNr = iRowNr + 1
  End If
  'Return #
  RowNr = iRowNr

End Function

Call it from a query like:

SELECT InvoiceNumber, RowNr([InvoiceNumber]) AS RowCount
FROM tblX;
cs2dataAuthor Commented:
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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