SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

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

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.

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