CODEWITHSUNDEEP
mysqlsqldate
tylerBrignone
tylerBrignone

Reputation: 45

How to select max date from a list of max date subqueries

I need to get the MAX date from a bunch of different dates across different tables. I have a query that returns me 6 dates. I need to somehow get that max of that query. Here is my first query that works and gives me the 6 different MAX dates:

    SELECT  
        MAX(mi.last_updated_dts),
        (SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156),
        (SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156),
        (SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156),
        (SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156),
        (SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156)
    FROM 
        member_info AS mi
    WHERE mi.id = 1276

Here is kind of what I'm trying to do but it doesn't work:

SELECT MAX(
             SELECT  
                  MAX(mi.last_updated_dts),
                  (SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156),
                  (SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156),
                  (SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156),
                  (SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156),
                  (SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id =1156)
             FROM 
                  member_info AS mi
             WHERE mi.id = 1276
          ) as theMostMaxDate

I don't know what to put in the from statement or if this is even possible. Thanks for any help!

Upvotes: 0

Views: 65

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

@jpw has the right solution for this problem. I just want to point out that you can do it without subqueries:

SELECT MAX(mi.last_updated_dts) AS max_date FROM member_info AS mi WHERE mi.id = 1276
UNION ALL
SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156
UNION ALL
SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156
UNION ALL
SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156
UNION ALL
SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156
UNION ALL
SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc 
INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156
ORDER BY max_date DESC
LIMIT 1;

Upvotes: 0

Shadow
Shadow

Reputation: 34231

Max() is a group by function and works on a single field or expression. Use greates() function instead, that works across multiple fields or expressions.

SELECT  
    GREATEST(MAX(mi.last_updated_dts),
    (SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156),
    (SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156),
    (SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156),
    (SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156),
    (SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156)) as maxdate
FROM 
    member_info AS mi
WHERE mi.id = 1276

Another solution is to combine the independent queries with union and you can use max() to select the overall maximum from the resultset.

Upvotes: 0

jpw
jpw

Reputation: 44871

One solution could be to rewrite the query to use union all in a derived table and get the max from that, although this would only get you a date and no information about the source of it (but if that is important you could add a column with a literal value indicating what query gave you the max date):

SELECT MAX(max_date) 
FROM (
    SELECT MAX(mi.last_updated_dts) AS max_date FROM member_info AS mi WHERE mi.id = 1276
    UNION ALL
    SELECT MAX(lt.created_dts) FROM live_training as lt WHERE lt.me_id = 1156
    UNION ALL
    SELECT MAX(gm.created_dts) FROM group_member as gm WHERE gm.me_id = 1156
    UNION ALL
    SELECT MAX(clm.created_dts) FROM contact_list_member as clm WHERE clm.me_id = 1156
    UNION ALL
    SELECT MAX(mc.created_dts) FROM member_case as mc WHERE mc.me_id = 1156
    UNION ALL
    SELECT MAX(mcc.created_dts) FROM member_case_comment as mcc 
    INNER JOIN member_case as mc ON (mcc.member_case_id = mc.id) WHERE mc.me_id = 1156
) t

Upvotes: 2

Related Questions