Combining ORDER BY AND UNION in SQL Server

Fetching first and last rows simultaneously

Extract the first and last rows from a table using ORDER BY with sub-queries and UNION ALL.

SELECT * FROM (
    SELECT TOP 1 * FROM Table ORDER BY Column ASC
    UNION ALL
    SELECT TOP 1 * FROM Table ORDER BY Column DESC
) AS MyTwoPennyRecords;


The query efficiently retrieves the top record (smallest) and the "peak" record (largest) in one fell swoop.

Effective use of TOP and ORDER BY

TOP and ORDER BY commands within sub-queries enable efficient extraction of special records:

• Use TOP 1 with ascending order to fetch the first record.
• Use TOP 1 with descending order to fetch the last record.

Combining these queries within a UNION ALL operation retains the order of records defined by the ORDER BY clauses.

UNION compatibility checklist

When leveraging UNION or UNION ALL, ensure these points:

• The number and order of selected columns in both SELECT statements should match.
• Employ column aliases if different column names are used, but are of compatible data types.

Advanced wizardry: CTEs and ROW_NUMBER

Common Table Expressions (CTEs) coupled with ROW_NUMBER() function provides granular control:

WITH NumberedRows AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Column) AS RowNum, *
    FROM Table
)
SELECT * FROM NumberedRows WHERE RowNum IN (1, (SELECT COUNT(*) FROM NumberedRows));


This method assigns a unique row number to each record, making it straightforward to filter for the first and last rows.

Dealing with single-row tables

When working with tables containing a single row, modify the queries within the UNION ALL to ensure no duplicate results sneaks into your output.

Remember efficiency

More than just magic spells, SQL queries should be efficient! Always use the best method (often translating to least resources used). Considering indexes on the column participating in sorting operation could mean an extra performance boost.

Be proactive with potential issues

Discrepancies in data types, column names, and null values can throw a wrench in your query. Always test your SQL enchantments to ensure they handle edge cases gracefully.