By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799039 fiddles created (41830 in the last week).
SELECT version();
version()
8.0.25
…
hidden batch(es)
CREATE TABLE user
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR (256) NOT NULL
);
✓
hidden batch(es)
CREATE TABLE login_history
(
lh_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL,
login_dt TIMESTAMP NOT NULL,
CONSTRAINT lh_user_dt_uq UNIQUE (user_id, login_dt), -- same user can't login at the same time
CONSTRAINT lh_uid_fk FOREIGN KEY (user_id) REFERENCES user (id),
KEY (user_id),
KEY (login_dt)
);
✓
hidden batch(es)
INSERT INTO user (email)
VALUES
('111@xyz.com'),
('222@xyz.com'),
('333@xyz.com'),
('444@xyz.com'),
('555@xyz.com'),
('666@xyz.com'); -- added following query about users who have never logged in!
SELECT * FROM user;
✓
id
email
1
111@xyz.com
2
222@xyz.com
3
333@xyz.com
4
444@xyz.com
5
555@xyz.com
6
666@xyz.com
…
hidden batch(es)
INSERT INTO login_history (user_id, login_dt)
VALUES
(1, '2011-01-11'), (1, '2011-02-11'), (1, '2011-03-11'),
(2, '2012-01-12'), (2, '2012-02-12'), (2, '2012-03-12'),
(3, '2013-01-13'), (3, '2013-02-13'), (3, '2013-03-13'),
(4, '2014-01-14'), (4, '2014-02-14'), (4, '2014-03-14'),
(5, '2015-01-15'), (5, '2015-02-15'), (5, '2015-03-15');
SELECT * FROM login_history; -- no record for user with email 666@xyz.com
✓
lh_id
user_id
login_dt
1
1
2011-01-11 00:00:00
2
1
2011-02-11 00:00:00
3
1
2011-03-11 00:00:00
4
2
2012-01-12 00:00:00
5
2
2012-02-12 00:00:00
6
2
2012-03-12 00:00:00
7
3
2013-01-13 00:00:00
8
3
2013-02-13 00:00:00
9
3
2013-03-13 00:00:00
10
4
2014-01-14 00:00:00
11
4
2014-02-14 00:00:00
12
4
2014-03-14 00:00:00
13
5
2015-01-15 00:00:00
14
5
2015-02-15 00:00:00
15
5
2015-03-15 00:00:00
…
✓
hidden batch(es)
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
ORDER BY id, rn;
rn
lh_id
id
ldt
1
3
1
2011-03-11 00:00:00
2
2
1
2011-02-11 00:00:00
3
1
1
2011-01-11 00:00:00
1
6
2
2012-03-12 00:00:00
2
5
2
2012-02-12 00:00:00
3
4
2
2012-01-12 00:00:00
1
9
3
2013-03-13 00:00:00
2
8
3
2013-02-13 00:00:00
3
7
3
2013-01-13 00:00:00
1
12
4
2014-03-14 00:00:00
2
11
4
2014-02-14 00:00:00
3
10
4
2014-01-14 00:00:00
1
15
5
2015-03-15 00:00:00
2
14
5
2015-02-15 00:00:00
3
13
5
2015-01-15 00:00:00
…
hidden batch(es)
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;
id
email
lh_id
ldt
1
111@xyz.com
3
2011-03-11 00:00:00
2
222@xyz.com
6
2012-03-12 00:00:00
3
333@xyz.com
9
2013-03-13 00:00:00
4
444@xyz.com
12
2014-03-14 00:00:00
5
555@xyz.com
15
2015-03-15 00:00:00
…
hidden batch(es)
SELECT u.id, u.email, MAX(lh.login_dt) AS "Last login"
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
GROUP BY u.id, u.email
ORDER BY u.id;
id
email
Last login
1
111@xyz.com
2011-03-11 00:00:00
2
222@xyz.com
2012-03-12 00:00:00
3
333@xyz.com
2013-03-13 00:00:00
4
444@xyz.com
2014-03-14 00:00:00
5
555@xyz.com
2015-03-15 00:00:00
6
666@xyz.com
…
hidden batch(es)
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
ORDER BY u.id DESC;
id
email
rn
lh_id
luid
ldt
6
666@xyz.com
1
5
555@xyz.com
1
15
5
2015-03-15 00:00:00
5
555@xyz.com
2
14
5
2015-02-15 00:00:00
5
555@xyz.com
3
13
5
2015-01-15 00:00:00
4
444@xyz.com
1
12
4
2014-03-14 00:00:00
4
444@xyz.com
2
11
4
2014-02-14 00:00:00
4
444@xyz.com
3
10
4
2014-01-14 00:00:00
3
333@xyz.com
1
9
3
2013-03-13 00:00:00
3
333@xyz.com
2
8
3
2013-02-13 00:00:00
3
333@xyz.com
3
7
3
2013-01-13 00:00:00
2
222@xyz.com
1
6
2
2012-03-12 00:00:00
2
222@xyz.com
2
5
2
2012-02-12 00:00:00
2
222@xyz.com
3
4
2
2012-01-12 00:00:00
1
111@xyz.com
1
3
1
2011-03-11 00:00:00
1
111@xyz.com
2
2
1
2011-02-11 00:00:00
1
111@xyz.com
3
1
1
2011-01-11 00:00:00
…
hidden batch(es)
SELECT * FROM
(
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1
ORDER BY tab.id;
id
email
rn
lh_id
luid
ldt
1
111@xyz.com
1
3
1
2011-03-11 00:00:00
2
222@xyz.com
1
6
2
2012-03-12 00:00:00
3
333@xyz.com
1
9
3
2013-03-13 00:00:00
4
444@xyz.com
1
12
4
2014-03-14 00:00:00
5
555@xyz.com
1
15
5
2015-03-15 00:00:00
6
666@xyz.com
1
…
hidden batch(es)
EXPLAIN ANALYZE
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;
EXPLAIN
-> Sort: u.id (actual time=0.172..0.246 rows=5 loops=1)
-> Stream results (cost=0.50 rows=0) (actual time=0.135..0.160 rows=5 loops=1)
-> Nested loop inner join (cost=0.50 rows=0) (actual time=0.132..0.150 rows=5 loops=1)
-> Index lookup on tab using <auto_key0> (rn=1) (actual time=0.002..0.004 rows=5 loops=1)
-> Materialize (cost=0.00..0.00 rows=0) (actual time=0.120..0.123 rows=5 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt desc ) (actual time=0.046..0.088 rows=15 loops=1)
-> Sort: lh.user_id, lh.login_dt DESC (cost=1.75 rows=15) (actual time=0.041..0.045 rows=15 loops=1)
-> Index scan on lh using lh_user_dt_uq (actual time=0.013..0.025 rows=15 loops=1)
-> Single-row index lookup on u using PRIMARY (id=tab.id) (cost=0.30 rows=1) (actual time=0.005..0.005 rows=1 loops=5)
…
hidden batch(es)
EXPLAIN ANALYZE
SELECT u.id, u.email, MAX(lh.login_dt) AS "Last login"
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
GROUP BY u.id, u.email
ORDER BY u.id;
EXPLAIN
-> Sort: u.id, u.email (actual time=0.140..0.141 rows=6 loops=1)
-> Table scan on <temporary> (actual time=0.001..0.002 rows=6 loops=1)
-> Aggregate using temporary table (actual time=0.127..0.129 rows=6 loops=1)
-> Nested loop left join (cost=2.95 rows=6) (actual time=0.024..0.060 rows=16 loops=1)
-> Table scan on u (cost=0.85 rows=6) (actual time=0.014..0.018 rows=6 loops=1)
-> Index lookup on lh using lh_user_dt_uq (user_id=u.id) (cost=0.27 rows=1) (actual time=0.005..0.006 rows=2 loops=6)
…
hidden batch(es)
EXPLAIN ANALYZE
SELECT * FROM
(
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1
ORDER BY tab.id;
EXPLAIN
-> Sort: tab.id (actual time=0.011..0.012 rows=6 loops=1)
-> Index lookup on tab using <auto_key0> (rn=1) (actual time=0.002..0.003 rows=6 loops=1)
-> Materialize (cost=0.00..0.00 rows=0) (actual time=0.189..0.191 rows=6 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt desc ) (actual time=0.108..0.136 rows=16 loops=1)
-> Sort: lh.user_id, lh.login_dt DESC (actual time=0.105..0.110 rows=16 loops=1)
-> Stream results (cost=2.95 rows=6) (actual time=0.030..0.084 rows=16 loops=1)
-> Nested loop left join (cost=2.95 rows=6) (actual time=0.024..0.059 rows=16 loops=1)
-> Table scan on u (cost=0.85 rows=6) (actual time=0.015..0.018 rows=6 loops=1)
-> Index lookup on lh using lh_user_dt_uq (user_id=u.id) (cost=0.27 rows=1) (actual time=0.004..0.006 rows=2 loops=6)
…
hidden batch(es)
SET PROFILING = 1;
✓
hidden batch(es)
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id;
id
email
lh_id
ldt
1
111@xyz.com
3
2011-03-11 00:00:00
2
222@xyz.com
6
2012-03-12 00:00:00
3
333@xyz.com
9
2013-03-13 00:00:00
4
444@xyz.com
12
2014-03-14 00:00:00
5
555@xyz.com
15
2015-03-15 00:00:00
…
hidden batch(es)
SELECT u.id, u.email, MAX(lh.login_dt) AS "Last login"
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
GROUP BY u.id, u.email
ORDER BY u.id;
id
email
Last login
1
111@xyz.com
2011-03-11 00:00:00
2
222@xyz.com
2012-03-12 00:00:00
3
333@xyz.com
2013-03-13 00:00:00
4
444@xyz.com
2014-03-14 00:00:00
5
555@xyz.com
2015-03-15 00:00:00
6
666@xyz.com
…
hidden batch(es)
SELECT * FROM
(
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1
ORDER BY tab.id;
id
email
rn
lh_id
luid
ldt
1
111@xyz.com
1
3
1
2011-03-11 00:00:00
2
222@xyz.com
1
6
2
2012-03-12 00:00:00
3
333@xyz.com
1
9
3
2013-03-13 00:00:00
4
444@xyz.com
1
12
4
2014-03-14 00:00:00
5
555@xyz.com
1
15
5
2015-03-15 00:00:00
6
666@xyz.com
1
…
hidden batch(es)
SET PROFILING = 0;
✓
hidden batch(es)
SHOW PROFILES;
Query_ID
Duration
Query
1
0.00069825
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn = 1
ORDER BY u.id
2
0.00073325
SELECT u.id, u.email, MAX(lh.login_dt) AS "Last login"
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
GROUP BY u.id, u.email
ORDER BY u.id
3
0.00079925
SELECT * FROM
(
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
) AS tab
WHERE tab.rn = 1
ORDER BY tab.id
…
hidden batch(es)
--
-- Examples of choosing last 2 logins
--
✓
hidden batch(es)
--
-- No NULLs because there's no LEFT JOIN
--
SELECT u.*, tab.lh_id, tab.ldt
FROM user u
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh_id,
lh.user_id AS id,
lh.login_dt AS ldt
FROM login_history lh
) tab
ON u.id = tab.id
WHERE tab.rn <= 2 -- could also have tab.rn IN (1, 2) - see below
ORDER BY u.id DESC, tab.rn;
id
email
lh_id
ldt
5
555@xyz.com
15
2015-03-15 00:00:00
5
555@xyz.com
14
2015-02-15 00:00:00
4
444@xyz.com
12
2014-03-14 00:00:00
4
444@xyz.com
11
2014-02-14 00:00:00
3
333@xyz.com
9
2013-03-13 00:00:00
3
333@xyz.com
8
2013-02-13 00:00:00
2
222@xyz.com
6
2012-03-12 00:00:00
2
222@xyz.com
5
2012-02-12 00:00:00
1
111@xyz.com
3
2011-03-11 00:00:00
1
111@xyz.com
2
2011-02-11 00:00:00
…
hidden batch(es)
--
-- There is a LEFT JOIN and the user who's never logged in is in the resultset.
--
SELECT * FROM
(
SELECT
u.id,
u.email,
ROW_NUMBER() OVER (PARTITION BY lh.user_id ORDER BY lh.login_dt DESC) AS rn,
lh.lh_id,
lh.user_id AS luid,
lh.login_dt AS ldt
FROM user u
LEFT JOIN login_history lh
ON u.id = lh.user_id
) AS tab
WHERE tab.rn IN (1, 2)
ORDER BY tab.id DESC;
