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://www.SQLAuthority.com),
pinaldave
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………………………………