Sql server remove duplicate and display latest data based on parameters-CodePudding

I have the following two tables: First is the result of my query, and the second one is the result that I want to achieve. I need help in displaying all the lastest data based on the transaction date for each concessionaire.

ConcessionaireId    EndReading  TransactionDate
               1    3606        9/1/2022
               1    3586        8/1/2022
               1    3565        7/1/2022
               2    4174        9/1/2022
               2    4163        8/1/2022
               2    4151        7/1/2022
               3    7125        9/1/2022
               3    7090        8/1/2022
               3    7051        7/1/2022
               4    6019        9/1/2022
               4    5992        8/1/2022
               4    5963        7/1/2022
               5    3165        9/1/2022
               5    3151        8/1/2022
               5    3139        7/1/2022


ConcessionaireId    EndReading  TransactionDate
               1    3606        9/1/2022
               2    4174        9/1/2022
               3    7125        9/1/2022
               4    6019        9/1/2022
               5    3165        9/1/2022




 SELECT top 100 percent
 ConcessionaireId, EndReading, FORMAT(TransactionDate, 'MM/dd/yyyy') AS 'TransactionDate'
 FROM Transactions
 WHERE  EXISTS (SELECT * FROM Transactions
 GROUP BY ConcessionaireId, Particulars) 
 AND ConcessionaireId IS NOT NULL AND Particulars = 'Billing'
 GROUP BY ConcessionaireId, EndReading, TransactionDate
 HAVING ConcessionaireId > 0
 ORDER BY ConcessionaireId,  MAX(CONVERT(int,(FORMAT(TransactionDate,'yyyyMMdd')))) DESC

CodePudding user response：

You can achieve this using ROW_NUMBER() Window function in SQL server

;With CTE AS(
SELECT top 100 percent
 ConcessionaireId, EndReading, FORMAT(TransactionDate, 'MM/dd/yyyy') AS 'TransactionDate',
 ROW_NUMBER() OVER(PARTITION BY ConcessionaireId ORDER BY TransactionDate DESC) AS RN
 FROM Transactions
 WHERE  EXISTS (SELECT * FROM Transactions
 GROUP BY ConcessionaireId, Particulars) 
 AND ConcessionaireId IS NOT NULL AND Particulars = 'Billing'
 GROUP BY ConcessionaireId, EndReading, TransactionDate
 HAVING ConcessionaireId > 0
 ORDER BY ConcessionaireId,  MAX(CONVERT(int,(FORMAT(TransactionDate,'yyyyMMdd')))) DESC)
SELECT ConcessionaireId, EndReading, TransactionDate
FROM CTE
WHERE RN = 1
ORDER BY ConcessionaireId