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

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. Khadar Khan came up with good solution using CTE SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL – Part 2. Today we will see the solution suggested by Dave Arthur. This solution is quite good as it uses UNION ALL instead of OR clause.

USE AdventureWorks
GO
SELECT A.*
FROM
(
SELECT TOP 1 *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID) A
UNION ALL
SELECT B.*
FROM
(
SELECT TOP 1 *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC) B
GO

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

About these ads

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

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

  2. Does TOP 1 truly follow the same SORT operator which ORDER BY does? TOP 1 use “TOP N Sort” operator and ORDER BY used SORT operator.” TOP 1 Sort” is a non blocking operator where as SORT is a blocking operator. Does these two operator work same way? Then why execution plan shows two different operators? What happen when the value of SalesOrderDetailID is tie for top 3 rows? Does SQL “TOP N Sort” return the first row? It will be very helpful if you answer my question. I post this question at Microsoft connect – http://connect.microsoft.com/SQLServer/feedback/details/778807/top-n-sort-and-sort-operator-returning-different-order-when-order-by-column-has-same-value. Did not get a satisfied answer.

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