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
Want to try this yourself? Here is how to install AdventureWorks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
Excellent!
Amazing. I was trying to simply UNION top 1 and top1 desc and it did not work.
Truely gr8 .
I faced same queries in many interviews !!!!!!!
Regards
Shivnath
it was nice………………………………
Good. Simple and worthy
how to retraive the lase row in the tabel
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 – https://docs.microsoft.com/en-us/collaborate/connect-redirect. Did not get a satisfied answer.
just great…