SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 2

Please read SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with alternate solution of the same problem.

Let us see following example provided by Khadar Khan. This example is using newly introduced concept of CTE of SQL Server 2005. CTE acts as temporary resultset for query.

WITH TopBottomRow
AS
(
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
UNION ALL
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
)
SELECT *
FROM TopBottomRow

Reference : Pinal Dave (http://blog.SQLAuthority.com), Khadar Khan

7 thoughts on “SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 2

  1. Thanks for sharing this.
    I was used to do the same as follows:

    SELECT * FROM
    (
    (SELECT MIN(SalesOrderDetailID) AS ID
    FROM Sales.SalesOrderDetail
    )
    UNION
    (SELECT MAX(SalesOrderDetailID) AS ID
    FROM Sales.SalesOrderDetail
    )
    ) AS t_TopBottomRow;

    Like

  2. Pingback: SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL - Part 3 Journey to SQL Authority with Pinal Dave

  3. Min Max solution would not work for retrieving more than top one or bottom row. The original solution however will be able to retrieve the top 10 as well as bottom 10.

    Deep…. There can be multiple scenarios for this. If you want to add values from existing table then you will have to write a statement like….

    INSERT INTO TABLE_NAME SELECT EXISTING_ROWS FROM ANOTHER_TABLE/TABLE_NAME

    To insert static values, you could do something like this….

    INSERT INTO TABLE_NAME
    SELECT 1,2,3,4,5
    UNION
    SELECT 6,7,8,9,10

    Like

  4. This is an very helpful solution however, can you show how we can do this with a Group By in the top and bottom queries. It appears I cannot use an OrderBy with a group by. .

    WITH TopBottomRow
    AS
    (
    SELECT TOP 1 SalesOrderDetailID, Sum (Sales)
    FROM Sales.SalesOrderDetail
    Group By SalesOrder
    ORDER BY SalesOrderDetailID
    UNION ALL
    SELECT TOP 1 SalesOrderDetailID, Sum(Sales)
    FROM Sales.SalesOrderDetail
    Group By SalesOrder
    ORDER BY SalesOrderDetailID DESC
    )
    SELECT *
    FROM TopBottomRow

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

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