Page 1 of 1
mysql: get max id from two tables
Posted: Sun Mar 05, 2006 3:35 pm
by pedroz
mysql query to know the max id in two tables
working:
Code: Select all
$m1 = $db->sql_fetchrow($db->sql_query("SELECT MAX(id) FROM users"));
$m2 = $db->sql_fetchrow($db->sql_query("SELECT MAX(id) FROM users_temp"));
$max = max($m1[0], $m2[0]);
echo $max
However...
I would like to know if there is a short query to get the $max value
Posted: Sun Mar 05, 2006 3:40 pm
by Chris Corbyn
You could try:
Code: Select all
select max(max(a.id), max(b.id)) as max_from_both from table_a as a, table_b as b
(Untested though)
Posted: Sun Mar 05, 2006 3:46 pm
by pedroz
Nop

...
Posted: Sun Mar 05, 2006 4:22 pm
by Chris Corbyn
Hmm... considering this works:
Code: Select all
select max(id) from a
union
select max(id) from b
I would have thought sticking it in a subquery would work:
Code: Select all
select max(
(select max(id) from a
union
select max(id) from b))
But I always get NULL. I bet timvw comes in any minute and regurgitates a little part of the MySQL manual in true text-book fashion

Posted: Sun Mar 05, 2006 4:26 pm
by pedroz
I am trying this without sucess
Code: Select all
$m2 = $db->sql_fetchrow($db->sql_query("(SELECT MAX(id) FROM users) UNION (SELECT MAX(id) FROM users_temp)"));
Posted: Sun Mar 05, 2006 4:27 pm
by Chris Corbyn
Code: Select all
select
max(id) as maxval
from
a
union
select
max(id) as maxval
from
b
order by
maxval desc
limit 1;
Works.
Posted: Sun Mar 05, 2006 4:28 pm
by Chris Corbyn
pedroz wrote:I am trying this without sucess
Code: Select all
$m2 = $db->sql_fetchrow($db->sql_query("(SELECT MAX(id) FROM users) UNION (SELECT MAX(id) FROM users_temp)"));
Don't start a query with syntax like a parentheses. It will fail. Union looks like I posted above

Posted: Sun Mar 05, 2006 4:48 pm
by pedroz
WORKING! THANKS