troubleshooting Question

Need a line number based on invoice number

Avatar of Russ Suter
Russ Suter asked on
Microsoft SQL Server* T-SQLSQL
8 Comments1 Solution96 ViewsLast Modified:
I seem to be a little rusty on some of my SQL. I can easily get a row number grouped by an invoice number as follows:
SELECT
    ROW_NUMBER() OVER (PARTITION BY INVOICENUMBER ORDER BY LINEITEMNUMBER) AS INVOICELINENUMBER,
    *
FROM
    INVOICES
WHERE
    DATECLOSED IS NULL
This handily gives me a unique line number starting with 1 for each invoice line item. What I can't seem to remember is how do I get another number that increments for each invoice number. So, say the first 2 rows in my data set have the same invoice number, they would both need to be assigned number 1. The next 3 rows are a different invoice number. They need to be number 2. Etc...
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros