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 :P

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