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

x
  • 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

0
cs2data
Asked:
cs2data
2 Solutions
 
JestersGrindCommented:
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


0
 
JestersGrindCommented:
Correction:

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

Greg


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

Open in new window

0
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.

 
dan_nealCommented:
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

0
 
dan_nealCommented:
You must use partition by to get the line number to restart at 1 for each invoiceno.
0
 
coffeeshopCommented:
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;
0
 
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.
0

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