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
Hello Sir,
Nice way to find first and last value. I have another way to have it, will work older version too. Check this out..
Declare @TestTab Table (ID TinyInt, Value Decimal(18,2))
INSERT INTO @TestTab (ID, Value)
SELECT 1, 1000
UNION ALL
SELECT 2, 2000
UNION ALL
SELECT 3, 3000
UNION ALL
SELECT 4, 3000
UNION ALL
SELECT 5, 4000
SELECT * FROM @TestTab
;WITH CTE_OUTPUT
AS
(
SELECT *
, ROW_NUMBER() OVER (ORDER BY ID ASC) SrAsc
, ROW_NUMBER() OVER (ORDER BY ID DESC) SrDesc
FROM @TestTab
)
SELECT ID, Value
FROM CTE_OUTPUT
WHERE (SrAsc = 1
OR SrDesc = 1)
Can we select all rows or n rows with TOP Keyword
Hi Himanshu,
If you see my solution, you can see the “n” as well. And if you want to fetch all rows which qualifies the condition then you can use RANK instead of ROW_NUMBER that’s it.
Thanks for your help Shivendra
Its my pleasure sir. Its all learnt from you. :)
Himanshu – Top 100 percent?
I have replied commnets please see my comments its working.
SELECT
COD_COLAB,
PRODUCTO,
VALOR_PLAN,
VALOR_VENTA,
UNIDADES_PLAN ,
UNIDADES_REALES ,
UNIDADES_BONIFICADAS,
UNIDADES_REALES/UNIDADES_PLAN AS CUMPLIMIENTO_UNIDADES,
VALOR_VENTA/VALOR_PLAN AS CUMPLIMIENTO_VALORES,
TOP10.ranking_Mes
FROM
(select –OBTIENE LOS 10 PRIMEROS DATOS DE VENTAS(TOP 10) EN EL QUIEBRE DE CADA COLABORADOR
COD_COLAB,
PRODUCTO,
SUM(VALOR_PLAN) AS VALOR_PLAN,
SUM(VALOR_VENTA) AS VALOR_VENTA,
SUM(UNIDADES_PLAN) AS UNIDADES_PLAN ,
SUM(UNIDADES_REALES) AS UNIDADES_REALES ,
SUM(UNIDADES_BONIFICADAS) AS UNIDADES_BONIFICADAS,
RANK() OVER (PARTITION BY COD_COLAB ORDER BY SUM(VALOR_VENTA) desc) AS ranking_Mes
from mobile.v_DashboardVentasRepresentante_SELLIN0001
group by
COD_COLAB, PRODUCTO
) AS TOP10
WHERE TOP10.ranking_Mes10
GROUP BY
COD_COLAB
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