如何在CTE中对HostStartTime列进行降序排序？

Got it, let's sort out your request to order the HostStartTime column in descending order in your CTE.

First, a quick heads-up: CTEs (Common Table Expressions) don't support ORDER BY clauses unless you're using TOP, OFFSET, or FETCH—since by definition, a CTE represents an unordered set of rows. The ORDER BY you had in your original CTE doesn't actually guarantee the order of the data when you query the CTE later. The only place that truly defines the final output order is the outer query's ORDER BY clause.

With that in mind, here's a cleaned-up version of your SQL that achieves your goal properly:

WITH FilterData AS (
    SELECT 
        CONVERT(VARCHAR(35), HostStartTime, 103) + ' ' + CONVERT(VARCHAR(35), HostStartTime, 24) AS HostStartTime,
        STUFF(reserve1, 5, 8, '********') AS CardNo,
        Reserve2 AS AccountNumber,
        CASE WHEN HostResponse = 'Y' THEN 'SUCCESS' ELSE 'FAILURE' END AS HostResponse,
        HostID
    FROM TBL_Host
    WHERE 
        HostStartTime >= @i_StartDate 
        AND HostStartTime <= @i_EndDate 
        AND HostID = 'GENERATE_PIN'
)
SELECT 
    HostStartTime, 
    ISNULL(CardNo, 'NA') AS CardNo, 
    AccountNumber, 
    HostResponse, 
    HostID
FROM FilterData
ORDER BY HostStartTime DESC;

Key Changes & Explanations: #

• Removed the redundant ORDER BY from the CTE: It wasn't serving any functional purpose here, as CTEs don't preserve row order without a limiting clause like TOP.
• Kept the ORDER BY HostStartTime DESC in the outer query: This is where you enforce the final sorted order of your result set.
• The formatted HostStartTime string will sort correctly chronologically: The 103 format (dd/mm/yyyy) combined with 24 (hh:mi:ss) creates a string that maintains chronological order when sorted lexicographically.

内容的提问来源于stack exchange，提问作者Sambit Sahoo