How to select bottom most rows?

It would seem that any of the answers which implement an ORDER BY clause in the solution is missing the point, or does not actually understand what TOP returns to you.

TOP returns an unordered query result set which limits the record set to the first N records returned. (From an Oracle perspective, it is akin to adding a where ROWNUM < (N+1).

Any solution which uses an order, may return rows which also are returned by the TOP clause (since that data set was unordered in the first place), depending on what criteria was used in the order by

The usefulness of TOP is that once the dataset reaches a certain size N, it stops fetching rows. You can get a feel for what the data looks like without having to fetch all of it.

To implement BOTTOM accurately, it would need to fetch the entire dataset unordered and then restrict the dataset to the final N records. That will not be particularly effective if you are dealing with huge tables. Nor will it necessarily give you what you think you are asking for. The end of the data set may not necessarily be "the last rows inserted" (and probably won't be for most DML intensive applications).

Similarly, the solutions which implement an ORDER BY are, unfortunately, potentially disastrous when dealing with large data sets. If I have, say, 10 Billion records and want the last 10, it is quite foolish to order 10 Billion records and select the last 10.

The problem here, is that BOTTOM does not have the meaning that we think of when comparing it to TOP.

When records are inserted, deleted, inserted, deleted over and over and over again, some gaps will appear in the storage and later, rows will be slotted in, if possible. But what we often see, when we select TOP, appears to be sorted data, because it may have been inserted early on in the table's existence. If the table does not experience many deletions, it may appear to be ordered. (e.g. creation dates may be as far back in time as the table creation itself). But the reality is, if this is a delete-heavy table, the TOP N rows may not look like that at all.

So -- the bottom line here(pun intended) is that someone who is asking for the BOTTOM N records doesn't actually know what they're asking for. Or, at least, what they're asking for and what BOTTOM actually means are not the same thing.

So -- the solution may meet the actual business need of the requestor...but does not meet the criteria for being the BOTTOM.

First, create an index in a subquery according to the table's original order using:

ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ) AS RowIndex

Then order the table descending by the RowIndex column you've created in the main query:

ORDER BY RowIndex DESC

And finally use TOP with your wanted quantity of rows:

    SELECT TOP 1 * --(or 2, or 5, or 34)
    FROM   (SELECT ROW_NUMBER() OVER (ORDER BY  (SELECT NULL) ) AS RowIndex, * 
            FROM MyTable) AS SubQuery
    ORDER BY RowIndex DESC

You can use the OFFSET FETCH clause.

SELECT COUNT(1) FROM COHORT; --Number of results to expect

SELECT * FROM COHORT 
ORDER BY ID
OFFSET 900 ROWS --Assuming you expect 1000 rows
FETCH NEXT 100 ROWS ONLY;

(This is for Microsoft SQL Server)

Official documentation: https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/

Querying a simple subquery sorted descending, followed by sorting on the same column ascending does the trick.

SELECT * FROM 
    (SELECT TOP 200 * FROM [table] t2 ORDER BY t2.[column] DESC) t1
    ORDER BY t1.[column]

SELECT TOP 10*from TABLE1 ORDER BY ID DESC

Where ID is the primary key of the TABLE1.