Post reply

selecting max date across several tables?

  • June 5, 2002 at 7:37 am

    #78771

    I have a column in several tables named startdate_d, I want to select the max(startdate_d) from all the tables but the function only wants to do it from one table, any suggestions?

    Thanks,

    Eddie

  • June 5, 2002 at 8:05 am

    #430361

    Union the tables and do the max on that.

    Andy

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • June 5, 2002 at 8:29 am

    #430362

    Actually depending on the number of tables you may want to do a subselect with unions (each union select does a max on it's own table). Then do a max on the union.

    Ex.

    SELECT MAX(startdate_d) AS MaxStartState FROM

    (

    SELECT MAX(startdate_d) AS startdate_d FROM tbl1

    UNION

    SELECT MAX(startdate_d) AS startdate_d FROM tbl2

    UNION

    SELECT MAX(startdate_d) AS startdate_d FROM tbl3

    ) AS MaxedUnionQuery

    This should take better advantage of the tables index if one exists on that field then overall max will have a smaller set to work with.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply