This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
The SELECT TOP(...) ... ORDER BY ... [ASC/DESC] is sufficient.
Please edit your question and add a use case.
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
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.
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.
@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.