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

Bisque

What engine? mysql, mssql? access? – Marchland

What do you mean by "doesn't seem to work" ? – Seventieth

Define "doesn't seem to work". What doesn't work? What does it return? What should it? – Alcaide

Is the id properly set up as the primary key? – Biopsy

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. – Overmantel

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. – Place

Answer 1

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;

Lupien

will this method still be valid if I have manually deleted row from the table? – Alethiaaletta

Answer 2

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.

Galliwasp

Answer 3

I have different solution:

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

Molest

Perfect solution if some records deleted. – Niersteiner

Answer 4

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.

Demonism

"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. – Demonism

Answer 5

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.

Enallage

Answer 6

echo $con->query("SELECT max(id) as max FROM table")->max;

or

$maxid = $con->query("SELECT max(id) as max FROM table")->max;

echo $maxid;

Vltava