max value in multiple tables 1

[aliashippysmom]

Hi! I can't seem to figure this out. I have five tables that contain a dateUpdated field. I need to find the maximum value of this field for all five tables. I want to execute a query which returns one value, the maximum date of all the dateUpdated fields.

I've tried things like this:

select max(dateUpdated) from
(
  select max(dateUpdated) as the_date
   from table1
  UNION
  select max(dateUpdated) as the_date
    from table2
  UNION
select max(dateUpdated) as the_date
   from table3
  UNION
  select max(dateUpdated) as the_date
    from table4
  UNION
  select max(dateUpdated) as the_date
    from table5)

Can anyone see what's wrong with this? It does not work. (I get a syntax error.) Thanks in advance.

 

[gmmastros]

You need to use the alias name in the outer query.

select max([!]the_date[/!]) from
(
  select max(dateUpdated) as the_date
   from table1
  UNION
  select max(dateUpdated) as the_date
    from table2
  UNION
select max(dateUpdated) as the_date
   from table3
  UNION
  select max(dateUpdated) as the_date
    from table4
  UNION
  select max(dateUpdated) as the_date
    from table5) As A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aliashippysmom]

I tried this:

select max(the_date)  from
(
  select max(dateUpdated) as the_date
   from BusUICont
  UNION
  select max(dateUpdated) as the_date
    from BusUICont
  UNION
  select max(dateUpdated)  as the_date
     from BusUI
  UNION
  select max(dateUpdated) as the_date
     from BusUICall
  UNION
  select max(dateUpdated) as the_date
     from BusUITax 
)	as my_date

I still cannot make this work. I must be missing something.

 

[gmmastros]

I don't notice anything wrong with the query (except the first 2 unions are coming from the same table ). Are you getting error messages? What is it? Or is this just producing the wrong results?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

The query looks OK
do you get an error or do you get results that you are not expecting

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aliashippysmom]

o.k. This worked:

SELECT     MAX(the_date) AS Expr1
FROM         (SELECT     MAX(dateUpdated) AS the_date
                       FROM          BusUIBen
                       UNION
                       SELECT     MAX(dateUpdated) AS the_date
                       FROM         BusUICont
                       UNION
                       SELECT     MAX(dateUpdated) AS the_date
                       FROM         BusUI
                       UNION
                       SELECT     MAX(dateUpdated) AS the_date
                       FROM         BusUICall
                       UNION
                       SELECT     MAX(dateUpdated) AS the_date
                       FROM         BusUITax) my_date

Seems like it didn't like the as my_date at the end of it all. I was getting a syntax error, but now I no longer get that and I get the correct results. Thanks to everyone!