MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT version();


version()


8.0.25


CREATE TABLE user
(
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR (256) NOT NULL
);
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)
  
);
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


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


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


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


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
null


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
null
null
null

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


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
null
null
null


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)



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)



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)



SET PROFILING = 1;
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


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
null


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
null
null
null


SET PROFILING = 0;
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


--
-- Examples of choosing last 2 logins 
--
--
-- 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


--
-- 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
null
null
null

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

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

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

id	email	rn	lh_id	luid	ldt
6	666@xyz.com	1	null	null	null
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

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