All geek questions in one place

Why is there no `select last` or` select bottom` in SQL Server, such as `select top`?

I know this may seem like a silly question, but please carry me. In the SQL server we have

SELECT TOP N ...

now that we can get the first n lines in ascending order (default), cool. If we want the records to be sorted in any other column, we simply indicate that the order by clause is something like this ...

 SELECT TOP N ... ORDER BY [ColumnName]

Even cooler. But what if I want the last line? I'm just writing something like this ...

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

But there is little concern in this. I said that caring is not a problem, because it is not a problem. That way I could get the last row based on this column, but what if I want the last row to be inserted. I know about SCOPE_IDENTITY , IDENT_CURRENT and @@IDENTITY , but consider heap (a table without clustered index ) without any identification column and multiple access from many places ( please do not go into it too much about how and when this multiple operation occurs , this does not apply to the main). So in this case, there seems to be no easy way to find which row was actually inserted last. Some may answer this as

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

Whatever you think about it, this is actually not the case , at least not always, and the one you can always rely on ( msdn , read the Advanced Scanning section ).

So the question comes down to this, as in the title itself. Why SQL Server Doesn't Have

 SELECT LAST

or say

 SELECT BOTTOM

or something like that, where we donā€™t need to specify Order By , and then it will give the last record inserted into the table during query execution (again I will not go into details about how this result is in case of uncommitted readings or phantom reads).

But if, nevertheless, someone claims that we cannot talk about this without discussing these levels of reading, then for them we could make him behave in the same way as the work of TOP , but just the opposite. But if we donā€™t need your argument, how can we always do

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

then I really don't know what to say here. I know that we can do this, but are there any correlation-based reasons or some kind of semantics-based reason or some other reason why we donā€™t have this SELECT LAST/BOTTOM or not. I am not looking for a way to do Order By , I am looking for a reason why it does not or not.

Extra I don't know much about how NOSQL works, but I worked (a bit) with mongodb and elastic search , and there also doesn't seem to be anything like that. Is it because they donā€™t have it, because no one has ever had this before, or is it somehow not believable?

UPDATE

I do not need to know that I need to specify the descending order or not. Please read the question and understand my concern before replying or commenting. I know how I will get the last line. This is not even a question, the main question comes down to why there is no SELECT LAST/BOTTOM like it.

UPDATE 2

After the answers of Vladimir and Pieter, I just wanted to update that I know the order is not guaranteed if I do SELECT TOP without Order By . I know, from what I wrote earlier in the question, it may seem that I do not know this case, but if you just look further, I gave a link to msdn and mentioned that SELECT TOP without Order By does not guarantee any ordering. Therefore, please do not add this to your answer that my statement is incorrect, as I already explained that I myself after a couple of lines (where I provided a link to msdn ).

+6
source share
4 answers

You can think of it this way.

SELECT TOP N without ORDER BY returns several rows of N , neither the first nor the last, only some. Which rows that it returns are not defined. You can run the same statement 10 times and get 10 different sets of lines each time.

So, if the server had the SELECT LAST N syntax, then the result of this statement without ORDER BY would again be undefined, which is exactly what you get with the existing SELECT TOP N without ORDER BY .

You emphasized in your question that you know and understand what I wrote below, but I will still keep it clear to everyone who reads this later.

Your first phrase in the question

The SQL server now has SELECT TOP N ... , in which we can get the first n rows in ascending order (default), cool.

wrong. With SELECT TOP N without ORDER BY you get N "random" rows. Well, not very random, the server does not randomly jump from line to line purposefully. He chooses a specific deterministic method of scanning through the table, but there can be many different ways to scan the table, and the server can freely change the selected path whenever he wants. This means "undefined".

The server does not track the order in which the rows were inserted into the table, so your assumption that the results of SELECT TOP N without ORDER BY determined by the order in which the rows were inserted into the table are incorrect.

So the answer to your last question

why there is no select last/bottom like it.

is an:

  • without ORDER BY SELECT LAST N results will be exactly the same as SELECT TOP N results - undefined.
  • with ORDER BY result of SELECT LAST N ... ORDER BY X ASC exactly the same as the result of SELECT TOP N ... ORDER BY X DESC .

So, it makes no sense to have two keywords that do the same thing.

There is a good point in Peter's answer: the word TOP somewhat misleading. This really means that the LIMIT result is set to a number of lines.

By the way, with SQL Server 2012 they added support for the ANSI OFFSET standard:

 OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [ FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY ]

When adding another keyword, it was justified that it is an ANSI AND standard, it adds important functionality - pagination, which did not exist before.

I would like to thank @ Razort4x here for providing a very good SELECT reference , cannot be guaranteed without an ORDER BY .

This concept is often misunderstood, and I saw many questions here about SO, which would be very helpful if they had a quote from this link.

The answer to your question

Why SQL Server does not have SELECT LAST or says SELECT BOTTOM or something like this, where we do not need to specify ORDER BY and that would give the last record inserted into the table at the time of the query (again, I will not go into details about whether this will be the result in the case of uncommitted readings or phantom).

:

The devil is in the details that you want to omit. In order to find out which record was the last inserted into the table at the time the query was executed (and in order to know this in a somewhat sequential / nonrandom way), the server needs to track this information in some way. Even if this is possible in all scenarios of several simultaneous transactions, it is most likely expensive in terms of performance. Not every SELECT requested this information (in fact, very little or not at all), but the overhead of tracking this information would always be there.

So you can think of it this way: by default, the server does nothing specific to know / track the order in which the rows were inserted, as this affects performance, but if you need to know that you can use, for example, the IDENTITY column . Microsoft could design the server mechanism so that an IDENTITY column is required in each table, but they made it optional, which, in my opinion, is good. I know better than a server which of my tables needs an IDENTITY column and which ones don't.

Summary

I would like to summarize that you can watch SELECT LAST without ORDER BY two different ways.

1) When you expect SELECT LAST to behave according to the existing SELECT TOP . In this case, the result is undefined for LAST and TOP , i.e. The result is virtually the same. In this case, it comes down to the (un) presence of another keyword. In this case, the developers of the language (T-SQL language) are always reluctant to add keywords, if there is no good reason for this. In this case, this can clearly be avoided.

2) If you expect SELECT LAST to behave like SELECT LAST INSERTED ROW . By the way, by the way, the same expectations expect SELECT TOP behave like SELECT FIRST INSERTED ROW or add new keywords LAST_INSERTED , FIRST_INSERTED to keep the existing TOP keyword intact. In this case, it comes down to performance and the added overhead of this behavior. The server currently avoids this performance penalty if you do not need this information. If you need it, IDENTITY is a pretty good solution if you use it carefully.

+14
source

There is no choice, because there is no need for it. Consider "select top 1 * from table". At the top of 1 you will get the first row to be returned. And then the process stops.

But there are no guarantees regarding the order unless you specify the order. That way, it can be any row in the dataset that you return.

Now do "select the last 1 * from the table". Now the database will have to process all the rows so you get the last. And since the order is not deterministic, it can also be the same result as when choosing "top 1".

See where the problem is? Without order from above and the latter, itā€™s actually the same, only the ā€œlastā€ will take longer. And with order, there really is only a need for the top.

 SELECT TOP N ...

now we can get the first n lines in ascending order (by default), cool. If we want the entries to be sorted in any other column, we simply indicate that order by is in order, something like this ...

What you say here is completely wrong and absolutely NOT working. In what order you do not receive any warranty. Ascending for 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 the last inserted record in this case.

Update

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

Update 2 Looking back, I was happier in the syntax used by mysql: LIMIT. Top says nothing about the order, and only there is indicated the number of returned rows.

Limits the rows returned from a query that are specified in the specified number of rows or percentage of rows in SQL Server 2014.

+3
source

Reasons why SELECT LAST_INSERTED doesn't make sense.

  • It cannot be easily applied to tables without heap.

  • These heaps can be freely moved by the DBMS, so these "natural" orders can be changed. To preserve this, the system needs an additional mechanism that seems like a waste.

  • If it is really necessary, it can be modeled by adding some ā€œauto-incrementā€ column.

+2
source

An SQL Server order is arbitrary unless otherwise specified. It is set based, so you must determine what your set is. Proper SCOPE_IDENTITY() is the correct way to capture the last inserted record or OUTPUT clause. Why are you doing heap inserts that you need to reference in chronological order? This is a super bad database design.

0
source

Source: https://habr.com/ru/post/986641/

More articles:


All Articles