Let us see following example provided by Khadar Khan. This example is using the newly introduced concept of CTE of SQL Server 2005. CTE acts as a temporary result set for the 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
Just a day ago, while working with some inventory related projects, I faced one interesting situation. I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If you specify more than one ORDER BY clause. It will give an error. Let us see how we can retrieve top and bottom rows together.
Let me know if there are any other methods to do the same tasks. I am very much interested to know if you know alternate solution.