0

I have this table below:

 +----+--------+------------+-----------+-------+
 | id | UserID | LoginDate  | LoginTime | Login |
 +----+--------+------------+-----------+-------+
 | 31 |     21 | 10/28/2020 | 9:32:36   |     1 |
 | 32 |     21 | 10/28/2020 | 10:38:30  |     1 |
 | 34 |      2 | 10/28/2020 | 11:09:48  |     1 |
 | 36 |      2 | 10/28/2020 | 11:10:09  |     1 |
 +----+--------+------------+-----------+-------+

I want to group by UserID and return only the rows that has the highest id value, expected output:

 +----+--------+------------+-----------+-------+
 | id | UserID | LoginDate  | LoginTime | Login |
 +----+--------+------------+-----------+-------+
 | 32 |     21 | 10/28/2020 | 10:38:30  |     1 |
 | 36 |      2 | 10/28/2020 | 11:10:09  |     1 |
 +----+--------+------------+-----------+-------+

This is my MySQL Code:

 SELECT * FROM (SELECT * FROM tbltrackerlogins ORDER BY id DESC) AS ttl GROUP BY UserID

I assumed the the inner select query already sorted out the result by id so that when it's grouped, it'll display the one with the highest ID. But the output is still like this:

 +----+--------+------------+-----------+-------+
 | id | UserID | LoginDate  | LoginTime | Login |
 +----+--------+------------+-----------+-------+
 | 31 |     21 | 10/28/2020 | 9:32:36   |     1 |
 | 34 |      2 | 10/28/2020 | 11:09:48  |     1 |
 +----+--------+------------+-----------+-------+

0 Answers0