CODEWITHSUNDEEP
mysql
ab11
ab11

Reputation: 20100

How to Select max date in this query?

I would like to write a query which retrieves name, id, and last modified date for each User. The below query gives the name, id, and last modified date from tables UserDetails1 and UserDetails2.

How could I modify this query to return a single date value, the max date for a given user_id in either of the details tables?

SELECT
    id,
    name,
    MAX(userdetails1.date_modified),
    MAX(userdetails2.date_modified)
FROM User user
INNER JOIN UserDetails1 userdetails1
    ON userdetails1.user_id = user.id
INNER JOIN UserDetails2 userdetails2
    ON userdetails2.user_id = user.id


User
id | name
---------
1  | name1
2  | name2
3  | name3

UserDetails1
user_id | date_modified
---------------------
1   | 2016-11-28 16:28:26
....

UserDetails2
user_id | date_modified
---------------------
1   | 2016-11-29 16:29:26
....

Upvotes: 0

Views: 66

Answers (2)

Gaurav Gupta
Gaurav Gupta

Reputation: 446

Try this, although I think there can be a more optimized way to write it.

SELECT
    id,
    name,
    (CASE 
        WHEN MAX(userdetails1.date_modified) > MAX(userdetails2.date_modified) 
        THEN MAX(userdetails1.date_modified)
        ELSE MAX(userdetails2.date_modified)
    END) 
FROM User user
INNER JOIN UserDetails1 userdetails1
    ON userdetails1.user_id = user.id
INNER JOIN UserDetails2 userdetails2
    ON userdetails2.user_id = user.id
GROUP BY id, name

Upvotes: 1

Fritz
Fritz

Reputation: 624

One option is to UNION your two date tables together. This can be done before or after you JOIN. I personally would UNION before JOINING as it is simpler in my mind and to write.

Please excuse the SQL Server-esque syntax.

Before JOINing:

SELECT
    u.id,
    u.name,
    MAX(d.date_modified) last_modified

FROM [User] u
    INNER JOIN 
        (
        SELECT user_id, date_modified
        FROM UserDetails1

        UNION ALL

        SELECT user_id, date_modified
        FROM UserDetails2
        ) d
        ON u.id = d.user_id

GROUP BY u.id, u.name

After JOINing:

SELECT 
    id, 
    name, 
    max(date_modified) last_modified

FROM
    (
    SELECT
        u.id, u.name, d.date_modified
    FROM [User] u
        INNER JOIN UserDetails1 d
        ON d.user_id = user.id

    UNION ALL

    SELECT
        u.id, u.name, d.date_modified
    FROM [User] u
        INNER JOIN UserDetails2 d
        ON d.user_id = u.id
    )

GROUP BY id, name

Upvotes: 0

Related Questions