In SQL, there is a query that selects the first records.

SELECT TOP 1000 ...

The beautiful side of the query is that you do not need to specify an index. You can select the latest entries, but already specify by what parameter

 SELECT * FROM ... WHERE ... ORDER BY table_id DESC

Is there a command to select the last records from the table, without specifying an index?

 SELECT BOTTOM 1000 ( такая команда не работает )

PS The answer is no such - the same answer.

  • You can find out the number of records through count and set the offset in the sample (offset) by 1000 less than count. - splash58
  • 9
    In principle, there is no order by notion of the first and last entries. DBMS does not guarantee the order of selection of records and the "last" records are not necessarily at the very end of the sample - Mike

1 answer 1

As rightly noted above in the comments to the question - the first and last entries do not exist, and the DBMS does not guarantee the order of the returned rows. TOP without specifying ORDER BY uses the internal data storage mechanism - by the name of the table we get access to the first data page, in the first page there is a link to the second, in the second - to the third, and so on.

Therefore, the engine, having received instructions to return the first lines without specifying the order, goes to the first page, reads the required number of lines, if there are not enough, it goes to the second page, reads the next batch, and does so until it reaches the required number, or the table will not end.

And, since the server does not guarantee any specific order of the returned data, it considers that, since you did not specify ORDER BY, it doesn’t matter to you which data set to get (otherwise you would have specified how to sort the data before returning the limited set) , and you can use the cheapest way to access data - there is no point in first getting to the last page from the links, and then starting to read the data from the end.