SELECT bottom SQL

Question

SELECT bottom SQL

Jonathan Brotto 1,076

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?

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Yitzhak Khabinsky 27,196 Reputation points

2022-01-18T19:39:13.107+00:00

@Jonathan Brotto ,

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;)

4 answers

Yitzhak Khabinsky 27,196 Reputation points

2022-01-18T19:39:13.107+00:00

@Jonathan Brotto ,

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;)

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

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

Answer 3

LiHong-MSFT 10,061

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.

Dave M 0 Reputation points

2026-09-01T18:41:31.17+00:00

@LiHong-MSFT

While I know this is an old thread and that there are many ways to do things and get the same results, I thought I should point out an instance where you can do a TOP(1) but can't do an ORDER BY but should be able to.

DELETE TOP(1)

FROM ....

WHERE ....

And yes I know that this thread is talking about SELECT, and that the work around is to use a secondary SELECT in the WHERE etc. but the existence of being able to do the above and NOT be able to control the ordering is a Largely Unnoticed Bug and Defect, since SQL Server is not guaranteed to return results in the exact same order every time without an ORDER BY.
Erland Sommarskog 137.4K Reputation points MVP Volunteer Moderator

2026-09-01T19:06:40.31+00:00
DELETE TOP(1) can be used to pop off a row from a queue table if you capture the row in an OUTPUT clause. The presumption is that you don't care which row you get.

If want to delete one row by specific criteria, you would do:

; WITH numbering AS ( SELECT row_number() OVER (ORDER BY somekey) AS rowno FROM tbl ) DELETE numbering WHERE rowno = 1

Another use for DELETE TOP is when you want to purge a large number of rows, but you want to do it batches. Something like:

WHILE @rowc = 10000 BEGIN DELETE TOP(10000) FROM tbl WHERE .... SELECT @rowc = @@rowcount END

Answer 4

Erland Sommarskog 137.4K MVP Volunteer Moderator

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