Re: how to find the maximum id from two tables

Frank Langelage said: > > 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. Do you want both maxima, or just the bigger of the two? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com sending to informix-list

"Obnoxio The Clown" <obnoxio@serendipita.com> wrote in message news:doekll$n0s$1@news.xmission.com... > > Frank Langelage said: >> >> 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. > > Do you want both maxima, or just the bigger of the two? Can you have a bigger maxima of the two?