We help IT Professionals succeed at work.

Need a line number based on invoice number

93 Views
Last Modified: 2018-10-25
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

Open in new window

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...
Comment
Watch Question

Russ SuterSenior Software Developer
CERTIFIED EXPERT

Author

Commented:
Perhaps to try and clarify my question. Here's a data example...
Sample DataThe first column was easy to compute using the ROW_NUMBER() function as I stated above. What I don't know is how to get the right values into the last column labeled INVOICECOUNT and highlighted in yellow in the above image.
CERTIFIED EXPERT

Commented:
I would think you will need to store the last invoice number and compare it with the current inv number.
If its not equal then increment the invoice count.
CERTIFIED EXPERT

Commented:
perhaps a sql function that takes in the invoice number and outputs the invoice count
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
RANK or DENSE_RANK

Back in a minute....
CERTIFIED EXPERT

Commented:
although maybe not....

discussion on partition query on so
https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Russ SuterSenior Software Developer
CERTIFIED EXPERT

Author

Commented:
Yep, that's it. I'd forgotten about DENSE_RANK(). Thanks very much!
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
My pleasure. Happy to have been able to help, even if just a memory jogger :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions