Max() across 2 tables in one query?

I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column "I
D".
I want to get the Max(ID) across both tables. I know I could declare
variable(s) and execute separate query for each table, but I would prefer to
keep it to one, simple query.
I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
from T2)
but that doesn't execute.You were close:
select Max(ID) from
(select Max(ID) from T1
union ALL
select Max(ID) from T2) as X
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
"ID".
I want to get the Max(ID) across both tables. I know I could declare
variable(s) and execute separate query for each table, but I would prefer to
keep it to one, simple query.
I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
from T2)
but that doesn't execute.|||select Max(ID) from (select Max(ID) id from T1 union all select Max(ID)
id from T2) t|||Use Pubs
Select (Select Max(Au_ID) From Authors) As 'MaxAuthorsID',
(Select Max(Emp_ID) From Employee) As 'MaxEmployeeID'
HTH
Barry|||Oops! Mis-read the post - I apologise!
It is Friday after all... ;-)
Barry|||Tom,
I guess this too will give an error...
May be it should be
select Max(ID) from
(select Max(ID) as ID from T1
union ALL
select Max(ID) from T2) as X
"Tom Moreau" wrote:

> You were close:
> select Max(ID) from
> (select Max(ID) from T1
> union ALL
> select Max(ID) from T2) as X
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
> news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
> I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
> "ID".
> I want to get the Max(ID) across both tables. I know I could declare
> variable(s) and execute separate query for each table, but I would prefer
to
> keep it to one, simple query.
> I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
> from T2)
> but that doesn't execute.
>
>|||Good catch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:C73B532C-9096-4438-8FC1-44AE770861B6@.microsoft.com...
Tom,
I guess this too will give an error...
May be it should be
select Max(ID) from
(select Max(ID) as ID from T1
union ALL
select Max(ID) from T2) as X
"Tom Moreau" wrote:

> You were close:
> select Max(ID) from
> (select Max(ID) from T1
> union ALL
> select Max(ID) from T2) as X
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
> news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
> I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
> "ID".
> I want to get the Max(ID) across both tables. I know I could declare
> variable(s) and execute separate query for each table, but I would prefer
> to
> keep it to one, simple query.
> I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
> from T2)
> but that doesn't execute.
>
>