how to find the maximum id from two tables
The JBossAS tries to execute this query and gets a syntax error: SELECT MAX(TXID) FROM ( SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES ) The goal is to find the maximum txid out of two tables with one query. I know I may create a view for the union select and select the max from this view. But I'd like to avoid this and use one query against the tables on my IDS 10 server. Regards, Frank
Frank Langelage wrote: > The JBossAS tries to execute this query and gets a syntax error: > SELECT MAX(TXID) FROM ( > SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS > UNION > SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES > ) > > The goal is to find the maximum txid out of two tables with one query. > I know I may create a view for the union select and select the max from > this view. > But I'd like to avoid this and use one query against the tables on my > IDS 10 server. Informix insists that you say "table(multiset(select ...))", so you'll need to rewrite you query as: SELECT MAX(TXID) FROM TABLE( MULTISET( SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES ) ) hth -- rh
Frank Langelage wrote:
> The JBossAS tries to execute this query and gets a syntax error:
> SELECT MAX(TXID) FROM (
> SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS
> UNION
> SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES
> )
>
> The goal is to find the maximum txid out of two tables with one query.
> I know I may create a view for the union select and select the max from
> this view.
> But I'd like to avoid this and use one query against the tables on my
> IDS 10 server.
>
> Regards,
> Frank
This will even work in 7.xx:
create function max2( one decimal, two decimal ) returning decimal; if one > two then
return one;
else
return two;
end if;
end function;
select max2((SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS),
(SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES))
from systables
where tabid = 1;
Art S. Kagel