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");

Define "doesn't seem to work". What doesn't work? What does it return? What should it? — CaffGeek, Jun 28 '10 at 15:27
Pls update your question with the output from: `DESC TABLE_A`, replacing "TABLE_A" with the name of the table you're wanting to get the latest ID value from. — OMG Ponies, Jun 28 '10 at 15:30
Be wary: depending on your situation, if more than one thread tries to do this at the same time, they could potentially all wind up using the same A value for B inserts, which may not be what you mean to do. Load testing is your friend. — 3Dave, Jun 28 '10 at 15:37

Mike · Accepted Answer · 2010-06-28T15:39:43.140

33

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;

edited Jun 28 '10 at 15:39

answered Jun 28 '10 at 15:33

Mike

20,127
2
38
65

will this method still be valid if I have manually deleted row from the table? – Weilory Aug 09 '20 at 11:14

score 9 · Answer 2 · answered Jul 29 '16 at 10:50

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.

score 2 · Answer 3 · edited Aug 28 '19 at 06:41

2

I have different solution:

SELECT AUTO_INCREMENT - 1 as CurrentId FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename'

edited Aug 28 '19 at 06:41

Butiri Dan

1,704
5
10
18

answered Aug 28 '19 at 04:59

Chris Turangan

21
2

Perfect solution if some records deleted. – HddnTHA Jul 15 '20 at 08:23

silent · Answer 4 · 2010-06-28T15:41:24.327

1

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.

edited Jun 28 '10 at 15:41

answered Jun 28 '10 at 15:35

silent

3,583
21
29

"for update" means that table is locked for write until select ends. Of course it will be better to make select of max value and insert in one transaction that locks these two tables. – silent Jun 28 '10 at 15:39

score 0 · Answer 5 · answered Jun 28 '10 at 15:34

0

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.

answered Jun 28 '10 at 15:34

GOsha

699
5
13

SELECT last id, without INSERT

5 Answers5