Update query for inserting row_count group-based

Posted on 2009-04-17
Last Modified: 2012-05-06
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

Question by:cs2data
    LVL 21

    Expert Comment

    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


    LVL 21

    Accepted Solution


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


    LVL 11

    Expert Comment

    by:N R
    Try this:
    update table
    set linenumber = row_number () over (order by InvoiceNumber, Item)
    from table

    Open in new window

    LVL 9

    Assisted Solution

    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

    LVL 9

    Expert Comment

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

    Expert Comment

    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;

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now