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

Advertisements
This entry was posted in Programming and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s