Re: how to find the maximum id from two tables

Captain Pedantic said: > > "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? "Bigger of the two numbers" for the pedants in the house. :o| -- 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