SELECT bottom SQL

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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