Select Top and Bottom Record using T-SQL

The example below shows how to select the top and bottom records based on a particular field.

In this case we have a WORK_ORDER table and we want the rows with the maximum and minimum TARGET_DATE.  The idea is to select the top 1 record sorted by TARGET_DATE (i.e. the earliest date) and UNION this with the top 1 record when sorted by TARGET_DATE descending (i.e. the latest date).


SELECT
    *
FROM
    (
    SELECT TOP 1
        *
    FROM dbo.WORK_ORDER
    ORDER BY TARGET_DATE
    UNION
    SELECT TOP 1
    *
    FROM dbo.WORK_ORDER
    ORDER BY TARGET_DATE DESC
) AS WORK_ORDERS

Be First to Comment

Leave a Reply