Why is there no `select last` or `select bottom` in SQL Server like there is `select top`?

Question

I know this might probably sound like a stupid question, but please bear with me. In SQL-server we have

SELECT TOP N ...

now in that we can get the first n rows in ascending order (by default), cool. If we want records to be sorted on any other column, we just specify that in the order by clause, something like this...

SELECT TOP N ... ORDER BY [ColumnName]

Even more cool. But what if I want the last row? I just write something like this...

SELECT TOP N ... ORDER BY [ColumnName] DESC

But there is a slight concern with that. I said concern and not issue because it isn't actually an issue. By this way, I could get the last row based on that column, but what if I want the last row that was inserted. I know about SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY, but consider a heap (a table without a clustered index) without any identity column, and multiple accesses from many places (please don't go into this too much as to how and when these multiple operation are happening, this doesn't concern the main thing). So in this case there doesn't seems to be an easy way to find which row was actually inserted last. Some might answer this as

If you do a select * from [table] the last row shown in the sql result window will be the last one inserted.

To anything thinking about this, this is not actually the case, at least not always and one that you can always rely on (msdn, please read the Advanced Scanning section).

So the question boils down to this, as in the title itself. Why doesn't SQL Server have a

SELECT LAST

or say

SELECT BOTTOM

or something like that, where we don't have to specify the Order By and then it would give the last record inserted in the table at the time of executing the query (again I am not going into details about how would this result in case of uncommitted reads or phantom reads).

But if still, someone argues that we can't talk about this without talking about these read levels, then, for them, we could make it behave as the same way as TOP work but just the opposite. But if your argument is then we don't need it as we can always do

SELECT TOP N ... ORDER BY [ColumnName] DESC

then I really don't know what to say here. I know we can do that, but are there any relation based reason, or some semantics based reason, or some other reason due to which we don't have or can't have this SELECT LAST/BOTTOM. I am not looking for way to does Order By, I am looking for reason as to why do don't have it or can't have it.

Extra I don't know much about how NOSQL works, but I've worked (just a little bit) with mongodb and elastic search, and there too doesn't seems to be anything like this. Is the reason they don't have it is because no one ever had it before, or is it for some reason not plausible?

UPDATE

I don't need to know that I need to specify order by descending or not. Please read the question and understand my concern before answering or commenting. I know how will I get the last row. That's not even the question, the main question boils down to why no select last/bottom like it's counterpart.

UPDATE 2

After the answers from Vladimir and Pieter, I just wanted to update that I know the the order is not guaranteed if I do a SELECT TOP without ORDER BY. I know from what I wrote earlier in the question might make an impression that I don't know that's the case, but if you just look a further down, I have given a link to msdn and have mentioned that the SELECT TOP without ORDER BY doesn't guarantees any ordering. So please don't add this to your answer that my statement in wrong, as I have already clarified that myself after a couple of lines (where I provided the link to msdn).

Answer 1

There is no select last because there is no need for it. Consider a "select top 1 * from table" . Top 1 would get you the first row that is returned. And then the process stops.

But there is no guarantees about ordering if you don't specify an order by. So it may as well be any row in the dataset you get back.

Now do a "select last 1 * from table". Now the database will have to process all the rows in order to get you the last one. And because ordering is non-deterministic, it may as well be the same result as from the select "top 1".

See now where the problem comes? Without an order by top and last are actually the same, only "last" will take more time. And with an order by, there's really only a need for top.

SELECT TOP N ...

now in that we can get the first n rows in ascending order (by default), cool. If we want records to be sorted on any other column, we just specify that in the order by clause, something like this...

What you say here is totally wrong and absolutely NOT how it works. There is no guarantee on what order you get. Ascending order on what ?

create table mytest(id int, id2 int)
insert into mytest(id,id2)values(1,5),(2,4),(3,3),(4,2),(5,1)
select top 1 * from mytest
select * from mytest
create clustered index myindex on mytest(id2)
select top 1 * from mytest
select * from mytest
insert into mytest(id,id2)values(6,0)
select top 1 * from mytest

Try this code line by line and see what you get with the last "select top 1".....you get in this case the last inserted record.

update

I think you understand that "select top 1 * from table" basically means: "Select a random row from the table". So what would last mean? "Select the last random row from the table?" Wouldn't the last random row from a table be conceptually the same as saying any 1 random row from the table? And if that's true, top and last are the same, so there is no need for last.

Update 2 In hindsight I was happier with the syntax mysql uses : LIMIT. Top doesn't say anything about ordering it is only there to specify the number of rows to be returned.

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2014.