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

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’.

Solarwinds

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

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

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



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

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Transfer The Logins and The Passwords Between Instances of SQL Server 2005
Next Post
SQL SERVER – Sharpen Your Basic SQL Server Skills – Learn the distinctions between unique constraint and primary key constraint and the easiest way to get random rows from a table

Related Posts

94 Comments. Leave new

  • Shivendra Kumar Yadav (MCP)
    February 25, 2015 1:08 am

    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)

    Reply
  • Himanshu Gupta
    March 24, 2015 7:07 pm

    Can we select all rows or n rows with TOP Keyword

    Reply
  • 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

    Reply
  • Pritesh Joshi
    May 18, 2016 6:12 pm

    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

    Reply
  • Pritesh Joshi
    May 18, 2016 6:16 pm

    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

    Reply
  • 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$

    ?

    Reply
  • nice i like very much

    Reply
  • 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

    Reply
  • neeraj prasad sharma
    February 22, 2017 12:04 pm

    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.

    Reply
  • 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])

    Reply
  • ;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

    Reply

Leave a Reply

Menu