mysql: combine 3 query in 1 - group by

Question

i have a database with 100 users and their login time (for 1 user there are 10000 rows). so how can i pick 3 users (by their userid) and then get only last time they logged in

(id, userid, timestamp)

SELECT userid,timestamp FROM users WHERE userid='22'  ORDER BY id DESC LIMIT 1 ;
SELECT userid,timestamp FROM users WHERE userid='15'  ORDER BY id DESC LIMIT 1 ;
SELECT userid,timestamp FROM users WHERE userid='42'  ORDER BY id DESC LIMIT 1 ;

i tried also GROUP BY but is not showing last timestamp for each user

SELECT userid,timestamp FROM users WHERE userid IN (22,15,42)' GROUP BY userid ORDER BY id DESC LIMIT 3 ;

score 1 · Answer 1 · answered Mar 10 '20 at 23:37

If the last login time is determined by the ID then:

SELECT u.userid, u.timestamp
FROM users u
WHERE u.userid IN (22,15,42) 
  AND u.ID = (SELECT MAX(ID) FROM users WHERE userid = u.userid)

or if it is determined by timestamp:

SELECT userid, max(timestamp) timestamp
FROM users 
WHERE userid IN (22,15,42) 
GROUP BY userid

score 0 · Answer 2 · answered Mar 10 '20 at 23:46

USE MAX of timestamp

SELECT userid,MAX(`timestamp`) 
FROM table1 
WHERE userid in(15,22,42) GROUP BY userid;

CREATE TABLE table1 (id int , userid int , `timestamp` timestamp)

✓

INSERT INTO table1 (id, userid, `timestamp`) VALUES  (1,15,NOW()),
(2,15,NOW() + INTERVAL 1 Minute),(3,15,NOW() + INTERVAL 5 Minute),
(4,22,NOW() + INTERVAL 1 Minute),
(5,22,NOW() + INTERVAL 2 Minute),(6,22,NOW() + INTERVAL 6 Minute),
(7,42,NOW() + INTERVAL 1 Minute),
(8,42,NOW() + INTERVAL 2 Minute),(9,22,NOW() + INTERVAL 7 Minute);

✓

SELECT userid,MAX(`timestamp`) FROM table1 WHERE userid in(15,22,42) GROUP BY userid;

userid | MAX(`timestamp`)   
-----: | :------------------
    15 | 2020-03-10 23:49:55
    22 | 2020-03-10 23:51:55
    42 | 2020-03-10 23:46:55

SELECT * FROM table1;

id | userid | timestamp          
-: | -----: | :------------------
 1 |     15 | 2020-03-10 23:44:55
 2 |     15 | 2020-03-10 23:45:55
 3 |     15 | 2020-03-10 23:49:55
 4 |     22 | 2020-03-10 23:45:55
 5 |     22 | 2020-03-10 23:46:55
 6 |     22 | 2020-03-10 23:50:55
 7 |     42 | 2020-03-10 23:45:55
 8 |     42 | 2020-03-10 23:46:55
 9 |     22 | 2020-03-10 23:51:55

db<>fiddle here

mysql: combine 3 query in 1 - group by

2 Answers2