Choosing the last row without any keys

Question

I need to get the last (newest) row in the table (using the natural order of MySQL - that is, what I get without any ORDER BY clause), however there is no key that I can execute ORDER BY on!

The only "key" in the table is the MD5 indexed field, so I cannot actually ORDER BY. There is no timestamp, auto-increment value, or any other field that I could easily ORDER. This is why I am left with the natural sort order as my “latest” indicator.

And, unfortunately, changing the table structure to add the correct auto-detection is out of the question. :(

Does anyone have any ideas on how w / plain SQL can do this, or am I SOL?

Answer 1

If it is MyISAM, you can do it in two queries

SELECT COUNT(*) FROM yourTable; SELECT * FROM yourTable LIMIT useTheCountHere - 1,1; 

This is unreliable, however, since

• It is assumed that rows are only added to this table and are never deleted.
• It is assumed that no other write to this table in the meantime (you can lock the table)
• MyISAM tables can be reordered using ALTER TABLE, so the insertion order is no longer maintained.

In InnoDB, it is not reliable, as this engine can reorder the table as it sees fit.

Answer 2

May I ask why you need this?

In oracle, it may be the same for MySQL, but the optimizer will select the fastest record / order to return the results. Thus, it is likely that your data was static in order to execute the same request twice and get a different response.

Answer 3

In principle, you cannot do this.

I usually suggest adding a surrogate primary key with auto-incrememt and ORDER BY, which:

 SELECT * FROM yourtable ORDER BY id DESC LIMIT 1 

But in your question you write ...

changing the table structure to add the correct auto-detection is out of the question.

So, another nice option that I can think of is to use a simulated ROW_NUMBER using variables:

 SELECT * FROM ( SELECT T1.*, @rownum := @rownum + 1 AS rn FROM yourtable T1, (SELECT @rownum := 0) T2 ) T3 ORDER BY rn DESC LIMIT 1 

Please note that this has serious performance implications: it requires a full scan, and the results are not guaranteed to be returned in any particular order in the subquery - you can get them in sort order, but again you cannot - when you don't set the order in which the server can select any order that he likes. Now he is likely to choose the order they store on disk to do as little work as possible, but relying on this is unreasonable.

Answer 4

Without an order by offer, you cannot guarantee the order in which you receive your result. The SQL engine is free to choose any order.

But if for some reason you still want to rely on this order, then, in fact, the last record from the result will be returned (only for MySql):

 select * from (select *, @rn := @rn + 1 rn from mytable, (select @rn := 0) init ) numbered where rn = @rn 

In an additional request, records are retrieved without order by , and they are assigned a serial number. The external request then selects only the one that received the last assigned number.

Answer 5

We can use availability for this task -

 SELECT MAX(id) as last_id,column1,column2 FROM table HAVING id=last_id;