Avatar of Russ Suter
Russ Suter

asked on 

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...
Microsoft SQL Server* T-SQLSQL

Avatar of undefined
Last Comment
Mark Wills
Avatar of Russ Suter
Russ Suter

ASKER

Perhaps to try and clarify my question. Here's a data example...
User generated imageThe 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.
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

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.
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

perhaps a sql function that takes in the invoice number and outputs the invoice count
Avatar of Mark Wills
Mark Wills
Flag of Australia image

RANK or DENSE_RANK

Back in a minute....
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

although maybe not....

discussion on partition query on so
https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Russ Suter
Russ Suter

ASKER

Yep, that's it. I'd forgotten about DENSE_RANK(). Thanks very much!
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo