Select last row in MySQL

Question

How can I SELECT the last row in a MySQL table?

I'm INSERTing data and I need to retrieve a column value from the previous row.

There's an auto_increment in the table.

Answer 1

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.

Answer 2

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.

Answer 3

If you want the most recently added one, add a timestamp and select ordered in reverse order by highest timestamp, limit 1. If you want to go by ID, sort by ID. If you want to use the one you JUST added, use mysql_insert_id.

Answer 4

Make it simply use: PDO::lastInsertId

http://php.net/manual/en/pdo.lastinsertid.php

Answer 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;

OR

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

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

Answer 6

SELECT * FROM adds where id=(select max(id) from adds);

This query used to fetch the last record in your table.