How to Select Last 5 Rows in a Table Without Sorting

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

Is there any way to fetch the last N rows from a MySQL table without using auto-increment field or timestamp?

In the SQL world, order is not an inherent property of a set of data.
Thus, you get no guarantees from your RDBMS that your data will come
back in a certain order -- or even in a consistent order -- unless you
query your data with an ORDER BY clause.

So if you don't have the data sorted by some id or some column then you cannot track the data based on its sorting. So it is not guaranteed how MYSQL will store the data and hence you cannot get the last n records.

You can also check this article:

Caveats

Ordering of Rows

In the absence of ORDER BY, records may be returned in a different
order than the previous MEMORY implementation.

This is not a bug. Any application relying on a specific order without
an ORDER BY clause may deliver unexpected results. A specific order
without ORDER BY is a side effect of a storage engine and query
optimizer implementation which may and will change between minor MySQL
releases.

Select physically last record without ORDER BY

to do a physically last record selection, you should use ctid - the tuple id, to get the last one - just select max(ctid). smth like:

t=# select ctid,* from t order by ctid desc limit 1;
  ctid  |               t               
--------+-------------------------------
 (5,50) | 2017-06-13 11:41:04.894666+00
(1 row)

and to do it without order by:

t=# select t from t where ctid = (select max(ctid) from t);
               t               
-------------------------------
 2017-06-13 11:41:04.894666+00
(1 row)

Its worth knowing that you can find ctid only after sequential scan. so checking the latest physically row will be costy on large data sets

How to SELECT the last 10 rows of an SQL table which has no ID field?

SQL tables have no implicit ordering, the order has to come from the data.
Perhaps you should add a field to your table (e.g. an int counter) and re-import the data.

However that will only give the order of the import and not the data. If your data has no ordering you have to find out how to add it.

EDIT: you say

...to make sure it imported everything.

What's wrong with using row count?

---

Related Topics