从左连接返回mysql查询中的最后一行

I am trying to get the last row of table2 that I left join with table1.

Table1 column:

1. id
2. name
3. created date

Table2 column:

1. id
2. last_login
3. ip_address

Login data stored into table2 everytime user logged in. So I am trying to display all users from table1 which is displaying the last_login record from table2.

Here is my current query :

SELECT table1.*, table2.last_login
FROM table1
LEFT JOIN table2 ON table2.id= table1.id
ORDER BY table2.last_login desc;

With the query above I am able to get all the data from both table, where if user A logged 5 times, the query will return 5 rows, but I wanted to only showing the user details and their last_login data. If I add GROUP BY table1.id it return 1 row record for every user but the last_login data is not showing the latest record.

If you just want the last login, perhaps a correlated subquery would do:

SELECT t1.*,
       (SELECT MAX(t2.last_login)
        FROM table2 t2
        WHERE t2.id = t1.id
       ) as last_login
FROM table1 t1
ORDER BY last_login desc;

For performance, you want an index on table2(id, last_login).

Try using MAX function in SQL

For reference: https://www.techonthenet.com/sql/max.php