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

User · Answer

on tables with many rows are two queries probably faster     SELECT  last id    MAX id  FROM table   SELECT   FROM table WHERE id    last id

User · Answer

SELECT   FROM adds where id  select max id  from adds     This query used to fetch the last record in your table

User · Answer

Make it simply use  PDO  lastInsertId  http   php net manual en pdo lastinsertid php

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt yourTable gt     and then use the obtained number in the LIMIT clause  SELECT   FROM orderbook2 LIMIT  lt number from rowcount gt   1   Unfortunately  MySQL will not allow for a sub-query  or user variable in the LIMIT clause

[mysql] Select last row in MySQL

Examples related to mysql