mysql: get max id from two tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

mysql: get max id from two tables

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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)
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

Post by pedroz »

Nop :( ...
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

Post 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)"));
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 :)
pedroz
Forum Commoner
Posts: 99
Joined: Thu Nov 03, 2005 6:21 am

Post by pedroz »

WORKING! THANKS
Post Reply