Return maximum date from 4 tables (in 1 query)

[Darren Bartrup]

I have four tables, each with a dteRefresh field which contains a date value.
Is there a way of returning the maximum date within the four tables in a single query.
I could do it by running a union query and then creating another query to return the maximum date, but I'm hoping there's a way of doing it in a single query - something like the following:

SELECT MAX(

SELECT dteRefresh
FROM tbl_CORE_AssessmentValidation

UNION SELECT dteRefresh
FROM tbl_CORE_CarersValidation

UNION SELECT dteRefresh
FROM tbl_CORE_JointValidation

UNION SELECT dteRefresh
FROM tbl_CORE_ReviewValidation

)

Thanks for any help.
Darren.

 

[pgc01]

Hi Darren

You can use a subquery. Your syntax was very close. Try:

SELECT MAX(dteRefresh) AS Max_dteRefresh
FROM (
    SELECT dteRefresh
    FROM tbl_CORE_AssessmentValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_CarersValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_JointValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_ReviewValidation
);

 

[Darren Bartrup]

It's so obvious when shown!
I really need to keep up to speed with my SQL.

Thanks for that PGC.

 

[Darren Bartrup]

Any idea why, after I've saved the query and go back in to look at it, it's showing:

SELECT MAX(dteRefresh) AS Max_dteRefresh
FROM [SELECT dteRefresh
    FROM tbl_CORE_AssessmentValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_CarersValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_JointValidation
 
    UNION SELECT dteRefresh
    FROM tbl_CORE_ReviewValidation
]. AS [%$##@_Alias];

The query still works, but I've never seen it do that before.

 

[pgc01]

Hi again

The subquery only exists inside this query. Since you did not name it, access names it with a default Alias. You need it to refer to the subquery, for ex. in an ORDER BY clause. In that case, however, you should replace the default Alias (that is meaningless) by your own, to improve readability.

 

[Darren Bartrup]

Again, pretty obvious when you think about it.

I was having one other problem, which is that the query worked before I saved it - but afterward I'd get an error message.
I solved this by taking out the spacing in the query.
Then I put the spaces back in so I could post what it was doing (removing part of the table name to show: tbl_CORE_Vali) but now it works fine with the line spacing to.

Of course I blame this on Bill Gates. The same as I blame him for brussel sprouts.