SQL, is there a way to return only 1 row after UNION

Question

For the specific needs of my application, I need to retrieve all the records between a date range and I also need the record that comes just before.

To do this, I use a UNION with the TOP 1 instruction in my second SELECT, but unfortunately the second SELECT returns all the records which meets the condition without taking into account the TOP 1.

SELECT
    T1.Date,
    T1.Order_Number
FROM Table1 T1
WHERE T1.idCustomer = 12345
AND T1.Date BETWEEN '20231101' AND '20231130'

UNION

SELECT TOP 1
    T2.Date,
    T2.Order_Number
FROM Table1 T2
WHERE T2.idCustomer = 12345
AND T2.Date < '20231101'
ORDER BY Date DESC, Order_Number DESC

Answer 1

You can use a subquery for the second part of your UNION to ensure that TOP 1 is applied correctly, and then apply an ORDER BY to the combined result.

SELECT
    T1.Date,
    T1.Order_Number
FROM Table1 T1
WHERE T1.idCustomer = 12345
AND T1.Date BETWEEN '2023-11-01' AND '2023-11-30'

UNION ALL

SELECT * FROM (
    SELECT TOP 1
        T2.Date,
        T2.Order_Number
    FROM Table1 T2
    WHERE T2.idCustomer = 12345
    AND T2.Date < '2023-11-01'
    ORDER BY T2.Date DESC, T2.Order_Number DESC
) AS PreviousRecord

ORDER BY Date, Order_Number;

UNION applies to the combined results of both queries, and the ORDER BY clause at the end of the statement affects the entire result set, not just the second query.