SELECT last id without INSERT

Question

I m trying to retrieve the id of one table A to insert into another table B  I cannot use last insert id   as i have not inserted anything into A  Any ideas on how to do this nicely    n   mysql query  SELECT max id  FROM tablename    doesn t seem to work  nor does   n   mysql query  SELECT max id  FROM tablename GROUP BY id

User · Answer

I have different solution   SELECT AUTO INCREMENT - 1 as CurrentId FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA    dbname  AND TABLE NAME    tablename

User · Answer

You could descendingly order the tabele by id and limit the number of results to one   SELECT id FROM tablename ORDER BY id DESC LIMIT 1   BUT  ORDER BY rearranges the entire table for this request  So if you have a lot of data and you need to repeat this operation several times  I would not recommend this solution

User · Answer

You can get maximum column value and increment it      InnoDB uses the following algorithm to   initialize the auto-increment counter   for a table t that contains an   AUTO INCREMENT column named ai col    After a server startup  for the first   insert into a table t  InnoDB executes   the equivalent of this statement   SELECT MAX ai col  FROM t FOR UPDATE        InnoDB increments by one the value   retrieved by the statement and assigns   it to the column and to the   auto-increment counter for the table    If the table is empty  InnoDB uses the   value 1    Also you can use SHOW TABLE STATUS and its  Auto increment  value

User · Answer

In MySQL  this does return the highest value from the id column   SELECT MAX id  FROM tablename    However  this does not put that id into  n    n   mysql query  SELECT max id  FROM tablename      To get the value  you need to do this    result   mysql query  SELECT max id  FROM tablename     if    result        die  Could not query     mysql error         id   mysql result  result  0   id      If you want to get the last insert ID from A  and insert it into B  you can do it with one command   INSERT INTO B  col  SELECT MAX id  FROM A

User · Answer

I think to add timestamp to every record and get the latest  In this situation you can get any ids  pack rows and other ops

[mysql] SELECT last id, without INSERT

Examples related to mysql

Examples related to lastinsertid