Select last row in MySQL

Solution 1:

Yes, there's an auto_increment in there

If you want the last of all the rows in the table, then this is finally the time where MAX(id) is the right answer! Kind of:

SELECT fields FROM table ORDER BY id DESC LIMIT 1;

Solution 2:

Keep in mind that tables in relational databases are just sets of rows. And sets in mathematics are unordered collections. There is no first or last row; no previous row or next row.

You'll have to sort your set of unordered rows by some field first, and then you are free the iterate through the resultset in the order you defined.

Since you have an auto incrementing field, I assume you want that to be the sorting field. In that case, you may want to do the following:

SELECT    *
FROM      your_table
ORDER BY  your_auto_increment_field DESC
LIMIT     1;

See how we're first sorting the set of unordered rows by the your_auto_increment_field (or whatever you have it called) in descending order. Then we limit the resultset to just the first row with LIMIT 1.

Solution 3:

You can combine two queries suggested by @spacepille into single query that looks like this:

SELECT * FROM `table_name` WHERE id=(SELECT MAX(id) FROM `table_name`);

It should work blazing fast, but on INNODB tables it's fraction of milisecond slower than ORDER+LIMIT.

Solution 4:

on tables with many rows are two queries probably faster...

SELECT @last_id := MAX(id) FROM table;

SELECT * FROM table WHERE id = @last_id;

Solution 5:

Almost every database table, there's an auto_increment column(generally id )

If you want the last of all the rows in the table,

SELECT columns FROM table ORDER BY id DESC LIMIT 1;

You can combine two queries into single query that looks like this:

SELECT columns FROM table WHERE id=(SELECT MAX(id) FROM table);