Return last line in mysql query from left join
I am trying to get the last row of table2 that I left, join table1.
Table 1:
- ID
- name
- date of creation
Table 2:
- ID
- last_login
- ip_address
Login data stored in table2 every time a user logs in. So I am trying to display all users from table1, which displays a record last_login
from table2.
Here is my current request:
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 can get all data from both tables, where if user A logged in 5 times, the query returns 5 rows, but I wanted to show only the user's data and their data last_login
. If I add GROUP BY table1.id
it will return 1 row record per user, but the data last_login
doesn't show the most recent record.
If you just want the last login, perhaps a correlated subquery:
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 need an index on table2(id, last_login)
.
Try using a function MAX
in SQL
For reference: https://www.techonthenet.com/sql/max.php