Need a line number based on invoice number

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...
LVL 22
Russ SuterSenior Software DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterSenior Software DeveloperAuthor 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.
Jonathan KellyCommented:
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.
Jonathan KellyCommented:
perhaps a sql function that takes in the invoice number and outputs the invoice count
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Mark WillsTopic AdvisorCommented:
RANK or DENSE_RANK

Back in a minute....
Jonathan KellyCommented:
although maybe not....

discussion on partition query on so
https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful
Mark WillsTopic AdvisorCommented:
Sorry about that... Use DENSE_RANK() function 
 SELECT
    ROW_NUMBER() OVER (PARTITION BY INVOICENUMBER ORDER BY LINEITEMNUMBER) AS INVOICELINENUMBER,
    dense_rank() OVER (order BY INVOICENUMBER) AS INVOICECOUNT,

    *
FROM
    INVOICES
WHERE
    DATECLOSED IS NULL

Open in new window

See : https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-2017

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russ SuterSenior Software DeveloperAuthor Commented:
Yep, that's it. I'd forgotten about DENSE_RANK(). Thanks very much!
Mark WillsTopic AdvisorCommented:
My pleasure. Happy to have been able to help, even if just a memory jogger :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.