Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

JFoushee

Programmer
Oct 23, 2000
200
US
Is it possible to do something similar to the following:
Code:
 SELECT Max(id) FROM
 (
 SELECT Max(id) FROM table1
UNION
 SELECT Max(id) FROM table2
 )

I've also tried something like this:

Code:
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.
 
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

 
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:
Code:
----------- 
400559

(1 row(s) affected)
 
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.

 
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:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top