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

Without ID in one query:

SELECT * FROM table_name LIMIT 1 OFFSET (SELECT COUNT(*) - 1 FROM table_name)

Answer 2

Many answers here say the same (order by your auto increment), which is OK, provided you have an autoincremented column that is indexed.

On a side note, if you have such field and it is the primary key, there is no performance penalty for using order by versus select max(id). The primary key is how data is ordered in the database files (for InnoDB at least), and the RDBMS knows where that data ends, and it can optimize order by id + limit 1 to be the same as reach the max(id)

Now the road less traveled is when you don't have an autoincremented primary key. Maybe the primary key is a natural key, which is a composite of 3 fields... Not all is lost, though. From a programming language you can first get the number of rows with

SELECT Count(*) - 1 AS rowcount FROM <yourTable>;

and then use the obtained number in the LIMIT clause

SELECT * FROM orderbook2
LIMIT <number_from_rowcount>, 1

Unfortunately, MySQL will not allow for a sub-query, or user variable in the LIMIT clause

Answer 3

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 4

You can use an OFFSET in a LIMIT command:

SELECT * FROM aTable LIMIT 1 OFFSET 99

in case your table has 100 rows this return the last row without relying on a primary_key

Answer 5

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;

Answer 6

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

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

Answer 7

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 8

Make it simply use: PDO::lastInsertId

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

Answer 9

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

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

SELECT * FROM table WHERE id = @last_id;

Answer 10

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 11

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.