How Do I Grab Only The Latest Invoice Number

I have Invoice Numbers that are stored as nvarchar(25). Their Format is ‘####AA’ Where #### is the Invoice Number and AA is the Version Number (partial Order Shipping) I cannot

Solution 1:

Try this:

SELECTInvoiceNumber + MAX(InvoiceString) AsstrInvoiceNoFROM
(
   SELECT
       dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
       dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
   FROM @TempTable
) AstblGROUPBYInvoiceNumber

Solution 2:

I dont think you need any UDF for this, a simple windowing function query should return what you looking for.

WITH x AS
(
 Select*
       ,ROW_NUMBER() OVER (PARTITIONBY InvoiceNumber ORDERBY strInvoiceNo DESC) rn 
 FROM TableName
)
SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM X 
WHERE rn =1

OR

SELECT strInvoiceNo, InvoiceNumber, InvoiceString 
FROM 
(
 Select*
       ,ROW_NUMBER() OVER (PARTITIONBY InvoiceNumber ORDERBY strInvoiceNo DESC) rn 
 FROM TableName
)x
WHERE rn =1

Solution 3:

Here is it in LINQ (Assuming fnStringFromNum returns a string padded on the left with spaces):

dbContext.YOURTABLE
  .GroupBy(x=>UDFFunctions.fnNumbersFromStr(x.AccountNumber))
  .Select(x=>x.OrderByDescending(y=>UDFFunctions.fnStringFromNum(y.AccountNumber).FirstOrDefault())

SQL (using current fnStringFromNum):

SELECTInvoiceNumber + LTRIM(MAX(RIGHT(SPACE(20)+InvoiceString,20))) AsstrInvoiceNoFROM
(
   SELECT
       dbo.fnNumbersFromStr(strInvoiceNo) AS [InvoiceNumber],
       dbo.fnStringFromNum(strInvoiceNo) AS [InvoiceString]
   FROM @TempTable
) AstblGROUPBYInvoiceNumber

Solution 4:

Not necessarily the most efficient, but this will work:

select strInvoiceNo
from@TempTable T
where InvoiceString = (selectmax(invoicestring) from temp1 where invoicenumber = T.invoicenumber)
orderby1

Edit: Sorry....disregard. This will work off of your full result table but may not be what you actually need. Apologies.