Mysql get second max date limit 1 base on user id joining table log

Question

I want to get second max login date from user_log table and returning login date to null when user_id records are not in user_log table and only single login date on user_log table.

for the example case.

user_log CASE 1

╔═════════╦═══════════╦═══════╦══════════════════════╗
║ ID      ║ USER_ID   ║ TYPE  ║  UPDATED_AT          ║
╠═════════╬═══════════╬═══════╬══════════════════════╣
║    1    ║ 1         ║ Login ║ 2019-06-19 15:00:00  ║
║    2    ║ 2         ║ Login ║ 2019-06-19 15:02:00  ║last login user_id 2
║    3    ║ 1         ║ Logout║ 2019-06-19 15:03:00  ║
║    5    ║ 1         ║ Login ║ 2019-06-19 15:04:00  ║last login user_id 1 
║    6    ║ 2         ║ Logout║ 2019-06-19 15:05:00  ║
║    7    ║ 3         ║ Login ║ 2019-06-19 15:06:00  ║last login user_id 3
║    8    ║ 2         ║ Login ║ 2019-06-19 15:07:00  ║
║    9    ║ 1         ║ Logout║ 2019-06-19 15:08:00  ║
║    10   ║ 1         ║ Login ║ 2019-06-19 15:10:00  ║
║    11   ║ 3         ║ Logout║ 2019-06-19 15:15:00  ║
║    12   ║ 3         ║ Login ║ 2019-06-19 15:18:00  ║
╚═════════╩═══════════╩═══════╩══════════════════════╝

user_log CASE 2

╔═════════╦═══════════╦═══════╦══════════════════════╗
║ ID      ║ USER_ID   ║ TYPE  ║  UPDATED_AT          ║
╠═════════╬═══════════╬═══════╬══════════════════════╣
║    1    ║ 1         ║ Login ║ 2019-06-19 15:00:00  ║last login user_id 1
║    2    ║ 1         ║ Logout║ 2019-06-19 15:03:00  ║
║    3    ║ 1         ║ Login ║ 2019-06-19 15:04:00  ║
║    4    ║ 2         ║ Login ║ 2019-06-19 15:05:00  ║
║    5    ║ 3         ║ Login ║ 2019-06-19 15:07:00  ║
╚═════════╩═══════════╩═══════╩══════════════════════╝

user_log CASE 3

╔═════════╦═══════════╦═══════╦══════════════════════╗
║ ID      ║ USER_ID   ║ TYPE  ║  UPDATED_AT          ║
╠═════════╬═══════════╬═══════╬══════════════════════╣
║    1    ║ 1         ║ Login ║ 2019-06-19 15:00:00  ║
║    2    ║ 2         ║ Login ║ 2019-06-19 15:05:00  ║
╚═════════╩═══════════╩═══════╩══════════════════════╝

table users

╔═════════╦════════════╗
║ ID      ║  USERNAME  ║
╠═════════╬════════════╣
║    1    ║   XX       ║
║    2    ║   YY       ║
║    3    ║   ZZ       ║
╚═════════╩════════════╝

This is my query.

SELECT u.id as iduser,u.username,ul.type,MAX(ul.updated_at) last_login_date FROM users u LEFT JOIN user_log ul ON u.id=ul.user_id where ul.type = 'Login' GROUP BY u.id

this query is just showing the max login date and only when user_id record in user_log table

Expected Result case 1

╔═══════════╦═══════════╦═══════╦══════════════════════╗
║ USER_ID   ║ USERNAME  ║ TYPE  ║  LAST_LOGIN_DATE     ║
╠═══════════╬═══════════╬═══════╬══════════════════════╣
║ 1         ║ XX        ║ Login ║ 2019-06-19 15:04:00  ║LOG_ID(5)
║ 2         ║ YY        ║ Login ║ 2019-06-19 15:02:00  ║LOG_ID(2)
║ 3         ║ ZZ        ║ Login ║ 2019-06-19 15:06:00  ║LOG_ID(7)
╚═══════════╩═══════════╩═══════╩══════════════════════╝

Expected Result case 2

╔═══════════╦═══════════╦═══════╦══════════════════════╗
║ IDUSER    ║ USERNAME  ║ TYPE  ║  LAST_LOGIN_DATE     ║
╠═══════════╬═══════════╬═══════╬══════════════════════╣
║ 1         ║ XX        ║ Login ║ 2019-06-19 15:00:00  ║LOG_ID(1)
║ 2         ║ YY        ║ -     ║ -                    ║LOG_ID(-)
║ 3         ║ ZZ        ║ -     ║ -                    ║LOG_ID(-)
╚═══════════╩═══════════╩═══════╩══════════════════════╝

Expected Result case 3

╔═══════════╦═══════════╦═══════╦══════════════════════╗
║ USER_ID   ║ USERNAME  ║ TYPE  ║  LAST_LOGIN_DATE     ║
╠═══════════╬═══════════╬═══════╬══════════════════════╣
║ 1         ║ XX        ║ -     ║ -                    ║LOG_ID(-)
║ 2         ║ YY        ║ -     ║ -                    ║LOG_ID(-)
║ 3         ║ ZZ        ║ -     ║ -                    ║LOG_ID(-)
╚═══════════╩═══════════╩═══════╩══════════════════════╝

all case should be one query.

score 1 · Accepted Answer · answered Jun 20 '19 at 10:35

Assume we have the following log entries for three users:

+----+---------+--------+---------------------+
| id | user_id | type   | updated_at          |
+----+---------+--------+---------------------+
|  1 |       1 | Login  | 2019-06-19 15:00:00 |
|  6 |       2 | Login  | 2019-06-19 15:02:00 |
|  2 |       1 | Logout | 2019-06-19 15:03:00 |
|  3 |       1 | Login  | 2019-06-19 15:04:00 |
|  7 |       2 | Logout | 2019-06-19 15:05:00 |
|  8 |       2 | Login  | 2019-06-19 15:07:00 |
|  4 |       1 | Logout | 2019-06-19 15:08:00 |
| 10 |       3 | Login  | 2019-06-19 15:09:00 |
|  5 |       1 | Login  | 2019-06-19 15:10:00 |
+----+---------+--------+---------------------+

Based on similar questions like What is the simplest SQL Query to find the second largest value? or Get the second highest value in a MySQL table you can first create a SELECT query which returns the last login entry, which would be the current active session/login:

SELECT
    user_id,
    MAX(updated_at) as max_updated
FROM
    user_log
WHERE
    type = 'Login'
GROUP BY
    user_id

This would give you the following result:

+---------+---------------------+
| user_id | max_updated         |
+---------+---------------------+
|       1 | 2019-06-19 15:10:00 |
|       2 | 2019-06-19 15:07:00 |
|       3 | 2019-06-19 15:09:00 |
+---------+---------------------+

You join this result on the user_log table again on the same user id.

SELECT
    a.user_id,
    a.type,
    a.updated_at,
    b.max_updated
FROM
    user_log a
LEFT JOIN
    (
        SELECT
            user_id,
            MAX(updated_at) as max_updated
        FROM
            user_log
        WHERE
            type = 'Login'
        GROUP BY
            user_id
    ) b ON a.user_id = b.user_id

This will get you the following result:

+---------+--------+---------------------+---------------------+
| user_id | type   | updated_at          | max_updated         |
+---------+--------+---------------------+---------------------+
|       1 | Login  | 2019-06-19 15:00:00 | 2019-06-19 15:10:00 |
|       1 | Logout | 2019-06-19 15:03:00 | 2019-06-19 15:10:00 |
|       1 | Login  | 2019-06-19 15:04:00 | 2019-06-19 15:10:00 |
|       1 | Logout | 2019-06-19 15:08:00 | 2019-06-19 15:10:00 |
|       1 | Login  | 2019-06-19 15:10:00 | 2019-06-19 15:10:00 |
|       2 | Login  | 2019-06-19 15:02:00 | 2019-06-19 15:07:00 |
|       2 | Logout | 2019-06-19 15:05:00 | 2019-06-19 15:07:00 |
|       2 | Login  | 2019-06-19 15:07:00 | 2019-06-19 15:07:00 |
|       3 | Login  | 2019-06-19 15:09:00 | 2019-06-19 15:09:00 |
+---------+--------+---------------------+---------------------+

You can use the max_updated column to remove any row where the updated_at column is the same. Such a row would be the "last" row, but since you don't want these rows (but instead all the previous rows), you filter them with a WHERE clause (and filter by the type as well).

SELECT
    a.user_id,
    a.type,
    a.updated_at,
    b.max_updated
FROM
    user_log a
LEFT JOIN
    (
        SELECT
            user_id,
            MAX(updated_at) as max_updated
        FROM
            user_log
        WHERE
            type = 'Login'
        GROUP BY
            user_id
    ) b ON a.user_id = b.user_id
WHERE
    a.updated_at != b.max_updated AND
    type = 'Login'

This will get you the following:

+---------+-------+---------------------+---------------------+
| user_id | type  | updated_at          | max_updated         |
+---------+-------+---------------------+---------------------+
|       1 | Login | 2019-06-19 15:00:00 | 2019-06-19 15:10:00 |
|       1 | Login | 2019-06-19 15:04:00 | 2019-06-19 15:10:00 |
|       2 | Login | 2019-06-19 15:02:00 | 2019-06-19 15:07:00 |
+---------+-------+---------------------+---------------------+

This looks promising as the row for the user 3 doesn't exists anymore, we have only Login entries and the login from the current/active session isn't selected. From here it is straight forward by using a GROUP BY and then a LEFT JOIN on the users table.

SELECT
    u.id,
    u.username,
    l.type,
    l.last_login_date
FROM
    users u
LEFT JOIN
(
    SELECT
        a.user_id,
        a.type,
        MAX(a.updated_at) AS last_login_date
    FROM
        user_log a
    LEFT JOIN
        (
            SELECT
                user_id,
                MAX(updated_at) as max_updated
            FROM
                user_log
            WHERE
                type = 'Login'
            GROUP BY
                user_id
        ) b ON a.user_id = b.user_id
    WHERE
        a.updated_at != b.max_updated AND
        type = 'Login'
    GROUP BY
        a.user_id) l ON u.id = l.user_id

This will get you the following result:

+------+----------+-------+---------------------+
| id   | username | type  | last_login_date     |
+------+----------+-------+---------------------+
|    1 | XX       | Login | 2019-06-19 15:04:00 |
|    2 | YY       | Login | 2019-06-19 15:02:00 |
|    3 | ZZ       | NULL  | NULL                |
+------+----------+-------+---------------------+

score 0 · Answer 2 · answered Jun 20 '19 at 04:36

0

SELECT DISTINCT date FROM tbl_date ORDER BY date DESC LIMIT 1,1;

you can use this query for getting second max date.

answered Jun 20 '19 at 04:36

daud

33
5

have you tried the code, and get the exact result so you post the answer? – Baba Carita Jun 20 '19 at 05:52

Mysql get second max date limit 1 base on user id joining table log

2 Answers2