SELECT bottom SQL

Jonathan Brotto 1,076 Reputation points
2022-01-18T18:16:40.54+00:00

There are times where I find Select TOP Order By [ASC/DESC] does not make sense but BOTTOM. Does SQL Server support the bottom command?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,641 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
{count} votes

3 answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-19T03:37:16.927+00:00

    Hi,@Jonathan Brotto

    Does SQL Server support the bottom command?

    No, SQL Server doesn’t support BOTTOM.
    First,
    We always need order by clause when using TOP or BOTTOM(if it is supported).
    If without ORDER BY ,results of SELECT BOTTOM N would be exactly the same as results of SELECT TOP N - undefined.
    Second,
    The result of SELECT BOTTOM N ... ORDER BY ... ASC is exactly the same as result of SELECT TOP N ... ORDER BY ... DESC.
    So, there is no need to have two key words that do the same thing.

    Actually, TOP is used to limit results to specific number of rows.
    As for another similar function keyword 'OFFSET', it is ANSI standard and it adds pagination.
    Please refer to this document for more details of OFFSET and ORDER BY.

    What I'm curious about is what situation makes you think Select TOP Order By [ASC/DESC] does not make sense
    If you want to retrieve TOP and BOTTOM Rows Together,please refer to this link .

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-18T22:53:18.8+00:00

    No, there is no BOTTOM operator.

    Rather than TOP you can do as in this example:

    SELECT *
    FROM   sys.objects
    ORDER BY name
    OFFSET 0 ROWS
    FETCH NEXT 5 ROWS ONLY 
    
    SELECT TOP 5 *
    FROM  sys.objects
    ORDER BY name
    

    These two queries return the same result.

    0 comments No comments

  3. s 7 1 Reputation point
    2022-06-03T05:33:27.237+00:00

    @Jonathan Brotto
    Hi Jon,

    If you want the last 100 rows but still in ascending order, you can use another select statement around the first:

    MSSQL

    select * from (select top 100 * from table order by date desc) a order by a.date;  
    

    MySQL

    select * from (select * from table order by date desc limit 100) a order by a.date;  
    

    Think of it this way: You are sorting the rows first and then selecting how many from the top of that sorted list.
    That should help it make sense to you.

    The MySQL command is maybe a bit more intuitive in that regard.

    0 comments No comments