>;WITH InvoiceTemp AS > (SELECT Invoice.Numero,Invoice.NoClient,Invoice.NoStatus, > ROW_NUMBER() OVER (PARTITION BY Invoice.NoClient ORDER BY AddDate DESC) AS RowNumber, > COUNT(*) OVER (PARTITION BY Invoice.NoClient) AS RowTotal > FROM Invoice) > >SELECT * FROM InvoiceTemp > WHERE InvoiceTemp.RowNumber<RowTotal AND InvoiceTemp.NoStatus=7 AND > EXISTS (SELECT * FROM InvoiceTemp InvoiceTemp2 > WHERE InvoiceTemp2.RowNumber=2 AND InvoiceTemp2.NoStatus=7 AND > InvoiceTemp.NoClient=InvoiceTemp2.NoClient) >>
SELECT * FROM InvoiceTemp WHERE InvoiceTemp.RowNumberbetween 2 and RowTotal AND InvoiceTemp.NoStatus=7However, this query will not select only consecutive last rows, it will select all rows except the very last one with the particular status. I need to think more about how to transform this query to only get consecutive last rows with that status and not all rows with that status if this is what you're after.