Clever Geek Handbook

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.

+3


source to share


2 answers


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)

.

0


source


Try using a function MAX

in SQL



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

0


source






More articles:


All Articles