SQL Server : getting DISTINCT TOP list from a another CTE query

Question

I have below queries

WITH Recent_BankAccountNumber_CTE (BankAccountNumber, TransactionId) AS 
(
    SELECT TOP 25 BankAccountNumber, TransactionId
    FROM [Transaction]
    WHERE CustomerId = 1234
    ORDER BY TransactionId DESC
)
SELECT 
    BankAccountNumber, TransactionId 
FROM 
    Recent_BankAccountNumber_CTE 
ORDER BY 
    OutboundTransactionId DESC

I get this output:

BankAccountNumber   OutboundTransactionId
------------------------------------------
8002950781          32078
8002950781          32077
8002950781          32076
016200270000486     32075
121054658698        32074
035200146349621     32073
200300019256        32072
200300019256        32071
200300019256        32070
200300019256        31088
018021030517        31087
102957612131        31086
100336018793        31085
100336018793        31084
042033430297001     31083
042033430297001     31082
100336018793        31081
8004062604          31080
102957612131        31079
042033430297001     31078
121054658698        31077
111353490614        31076
121054658698        31075
82455039            31074
110854031395        31073

What I am after is

As soon as I do (instead of above )

SELECT DISTINCT TOP 10 (BankAccountNumber) 
FROM Recent_BankAccountNumber_CTE

I get below, which is incorrect

BankAccountNumber
-----------------
016200270000486
018021030517
035200146349621
042033430297001
100336018793
102957612131
110854031395
111353490614
121054658698
200300019256

Can someone please let me know what is wrong with DISTINCT TOP 10?

user_14267425 · Answer

The 'top 10' component usually needs a sort. In your case, it doesn't have them so (apparently) is doing an alphabetic sort (hence 0s first, then 1s, then 2s).

You therefore need a value to sort on from your CTE. ~~One way is to use ROW_NUMBER() in the CTE.~~ We can just use TransactionId for this. We will then then need to change the DISTINCT to a GROUP BY so we can sort on the MAX() of that TransactionId (so we get the accounts most recently accessed)

WITH Recent_BankAccountNumber_CTE (BankAccountNumber, TransactionId) AS (
                 SELECT TOP 25 BankAccountNumber, TransactionId
                 FROM  [Transaction]
                 WHERE CustomerId = 1234
                 ORDER BY TransactionId desc)
SELECT   TOP 10 BankAccountNumber
FROM     Recent_BankAccountNumber_CTE 
GROUP BY BankAccountNumber
ORDER BY MAX(TransactionId) DESC;

Here's an updated db<>fiddle with original and this answer - noting that I had to make a few tweaks to use the data as the CTE.

tl;dr version: You need to put a sort into it to get the most recent transactions - in this case, MAX(TransactionID) DESC. However, you need to change the DISTINCT approach to GROUP BY as the MAX() part doesn't work with DISTINCT.

UPDATE: Version without a CTE

Note it may be possible to simplify the above to not need a CTE. I am assuming, however, that the CTE is potentially broader than the minimal version in this question, and the CTE makes it relatively clear what's going on and performance would probably be fine as long as you have an index on transaction.CustomerId (indeed, the performance could be better than the version below).

However, if desired, you should be able to do it with something like

SELECT   TOP 10 BankAccountNumber
FROM     [Transaction]
WHERE    CustomerId = 1234
GROUP BY BankAccountNumber
ORDER BY MAX(TransactionId) DESC;

SQL Server : getting DISTINCT TOP list from a another CTE query

1 Answers