I have to tables, one Users and one Visitors. In the table users I have al the user information and in the table visitors I put the login results. So for everytime a user logs in a new records gets into the table visitors with the user_id and datetime.

Now I want to select all the users with the datetime of the last login. How can i manage this?

Thanks, but i didnt get it working. I edited all the fields but i get an error. This is what i tried: SELECT u.users_id, u.users_firstname, u.users_lastname, u.users_company, u.users_email (SELECT datetime from visitors v where u.users_id = v.user_id order by datetime desc limit 1) as lastlogin_datetime from users u and this is the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT datetime from visitors v where u.users_id = v.user_id order by datetime d' at line 2
–
Leon van der VeenJan 17 '12 at 12:02

-1 This is not a scalable query - if there were 1M users, this would result in 1M subqueries. See my answer for how to do it properly.
–
Bohemian♦Jan 17 '12 at 12:04

probaly the problem is in the limit for mysql. Try instead of "limit 1", "limit 0,1"
–
Andre MarianoJan 17 '12 at 12:07

@AndreMariano, I got it working now. Thank you very much.
–
Leon van der VeenJan 17 '12 at 13:11

To avoid using subqueries to calculate the most recent time for every user (which would effectively mean running n queries for n users), use the mysql "trick" of a group bywithout aggregation, which returns the first row of each group:

This query will perform very well, making just one pass over the visits table 9there are no subqueries for each user).

Here's the break down of what's going on:

The inner-most query (aliased as x) gets user id and last_visit in order with the last_visit value we want first within each user id

The next query (aliased as y) uses a group by on user id without using any aggregating functions. In other databases this would be an error, but with mysql it returns the first row of every group - ie the last_visit value we want (having just ordered the rows thus)

The final, outer-most query does a left join on these results, giving a null value for users that have never visited, otherwise giving the last_visit we want joined to the user row

Won't work: table visits should be visitors; at least based on the OP. but the concept is solid
–
xQbertJan 17 '12 at 12:13

Thanks for your help but i didn't get it working. Before the alias y, do I need to put there the table visitors? I tried this: select u.*, datetime from users u left join (select user_id, datetime from (select u.users_id, v.datetime from users u join visitors v on v.user_id = u.users_id order by 1, 2 desc ) x group by 1) visitors y on y.user_id = u.users_id; This is the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'y on y.user_id = u.users_id LIMIT 0, 30' at line 9
–
Leon van der VeenJan 17 '12 at 12:51

SELECT cur.textID, cur.fromEmail, cur.subject,
cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
on cur.fromEmail = next.fromEmail
and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID'
ORDER BY LOWER(cur.fromEmail)
Basically, you join the table on itself, searching for later rows.

In the where clause you state that there cannot be later rows. This gives you only the latest row.

If there can be multiple emails with the same timestamp,
this query would need refining. If there's an incremental ID column in the email table,
change the JOIN like:

LEFT JOIN incomingEmails next
on cur.fromEmail = next.fromEmail
and cur.id < next.id