Select Max(id) FROM (Select Max UNION Select Max)

[JFoushee]

Is it possible to do something similar to the following:

 SELECT Max(id) FROM
 (
 SELECT Max(id) FROM table1
UNION
 SELECT Max(id) FROM table2
 )

I've also tried something like this:

SELECT Max(id) FROM table1 INTO @inttest1
SELECT Max(id) FROM table2 INTO @inttest2

If @inttest1 > @inttest2
  Begin
    @intmaxpk = @inttest1
  End
Else
  Begin
    @intmaxpk = @inttest2
  End

SELECT @intmaxpk

But SQL Server doesn't like my use of the INTO clause. I would like to get back one record with the definitive max.

 

[bperry]

Sure. Just a couple of very minor syntax errors

SELECT Max(id) FROM
(
SELECT Max(id) as id FROM table1
UNION
SELECT Max(id) FROM table2
) as dt


-----------
bperry

 

[JFoushee]

Amazing, what does the "as dt" clause do, and why was it so important? The query failed without "as dt", yet it didn't rename the column to "dt."

I just get this result:

----------- 
400559

(1 row(s) affected)

 

[bperry]

SELECT Max(id)
FROM

(
SELECT Max(id) as id FROM table1
UNION
SELECT Max(id) FROM table2
) as dt


The main FROM (in red) is selecting from a table (in bold) that doesn't really exist at all. It's just derived in memory for the duration of this statement. But, SQL can't select FROM it unless it has a name (i.e. all tables must have names, even these artificial tables), so I call it dt (for derived table). The name doesn't really matter: we could call it jfous, just as long as it has some name, so that SQL can refer to it.

 

[tlbroadbent]

If you are returning the result set to another application a named column may be required. You can also add an alias to the final result. In this case, I aliased the resulting column as MaxID. Though SQL doesn't care what the name is, as long as it follows the rules for identifiers, I like to use meaningful names.

SELECT Max(id) As MaxId
FROM

(
SELECT Max(id) as id FROM table1
UNION
SELECT Max(id) FROM table2
) as dt Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

Yes, that's a good suggestion.