clear markdown feedback
clear markdown feedback
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;
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
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
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
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
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
 hidden batch(es)