Mysql – Selecting last row WITHOUT any kind of key

MySQL

I need to get the last (newest) row in a table (using MySQL's natural order – i.e. what I get without any kind of ORDER BY clause), however there is no key I can ORDER BY on!

The only 'key' in the table is an indexed MD5 field, so I can't really ORDER BY on that. There's no timestamp, autoincrement value, or any other field that I could easily ORDER on either. This is why I'm left with only the natural sort order as my indicator of 'newest'.

And, unfortunately, changing the table structure to add a proper auto_increment is out of the question. 🙁

Anyone have any ideas on how this can be done w/ plain SQL, or am I SOL?

Best Answer

If it's MyISAM you can do it in two queries

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

This is unreliable however because

  1. It assumes rows are only added to this table and never deleted.
  2. It assumes no other writes are performed to this table in the meantime (you can lock the table)
  3. MyISAM tables can be reordered using ALTER TABLE, so taht the insert order is no longer preserved.

It's not reliable at all in InnoDB, since this engine can reorder the table at will.

Related Topic