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.
Incorrect T-SQL Script which will give an error.
-- This script will give you error USE AdventureWorks GO SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID UNION ALL SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID DESC GO
ResultSet:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.
As I was in a rush, I wrote something very simple with using UNION code, which will give me similar results.
Correct SQL Script which will give correct output.
Correct Script Method 1:
USE AdventureWorks GO SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID IN ( SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID FROM Sales.SalesOrderDetail UNION ALL SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID FROM Sales.SalesOrderDetail) GO
Correct Script Method 2:
USE AdventureWorks GO SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID IN ( SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID) OR SalesOrderDetailID IN ( SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID DESC) GO
I am sure there are more alternative methods to do the same, I encourage my readers to share their opinion and comments along with code. If your code is good, I will publish on this blog with your name.
Reference : Pinal Dave (https://blog.sqlauthority.com)
94 Comments. Leave new
select top 1 StudentId,Name from Student
union all
select StudentId,Name from (
select top 1 StudentId,Name from Student order by StudentId desc)as Std
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT SalesOrderDetailID from(select top 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC) as Std
how could i do the same but for each brand and item changes?
ETC:
Chocolatebrand Snickers 1.0 2$
Chocolatebrand Snickers 2.0 5$
Chocolatebrand2 Twix 1.0 1$
Chocolatebrand 2 twix 2.0 5$
?
nice i like very much
hi sir,my self ganesh,iam facing the problem when ever execute this query,
my error is
* SELECT TOP 5 * FROM EMP
SQL> /
SELECT TOP 5 * FROM EMP
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
plz solve this,my version is oracle 11g interface
i believe here query optimizer has a opportunity to do it in just one phase if the query wriiten clevery to push the QO for one phase only. one phase means just one set of scanning one set of filtirring one set or aggrigation or sort (based on final costing model),
so i tried it with CTE version of it.
;With OnePhase AS
(
Select [TOP] = MAX(COL1) , [BOTTOM] = MIN(COL1) from SOMETABLE
)
Select [TOP] from OnePhase
UNION ALL
Select [BOTTOM] from OnePhase
Watched the execution plan and nope it was 2 set of operations ans finally concation.
i put there type of execution in Missed Opportunity by QO. but please mind you i love QO
as i know how beutifull and powerfull SQL SERVER QO is so there is a better alternative for
this should be.
declare @t as table (top1 int , bottom1 int)
select max(SearchCol ) T , min(SearchCol ) B into #t from OuterTable
select [Top] = t from #t
Union all
select [Bottom] =b from #t
Used Single pass only.
Good one. Thanks for sharing.
This avoids Merge Join and Table Insert:
;WITH CTE AS (
SELECT MIN(SalesOrderDetailID) AS [MIN_SalesOrderDetailID], mAX(SalesOrderDetailID) AS [MAX_SalesOrderDetailID] FROM Sales.SalesOrderDetail)
SELECT *
FROM Sales.SalesOrderDetail
INNER JOIN CTE ON SalesOrderDetailID IN (CTE.[MIN_SalesOrderDetailID], CTE.[MAX_SalesOrderDetailID])
;with cte as
(
select top 1 * from table1 order by 1 desc
)
select * from cte
union
select top 1 * from table1 order by 1 asc