[thelist] Last Login Time SQL

Ken Snyder ksnyder at coremr.com
Tue Jan 9 14:06:08 CST 2007


Noah St. Amand wrote:
> Hi,
>
> I'm trying to come up with an SQL statement that will return all of the 
> users who haven't logged in to a system in 90 days or more. I can get 
> the information I need with a nested query, but given that there are a 
> lot of records, it's pretty slow and inefficient. There must be a good 
> way to do it, but I'm not fluent enough in SQL to figure it out.
>
> The details: I have two tables, one of which, "users", stores typical 
> user data (user_id, user_name, password, email) and the other, 
> "user_log" just records logins (user_id, login_time). I need to get a 
> list of all the user_names of people whose most recent login_time is 90 
> days or more before right now.
>   
How about something like this:

SELECT * FROM users WHERE id NOT IN(
  SELECT DISTINCT user_id FROM user_log
  WHERE login_time < '2006-10-10'
)

The distinct may or may not speed up the query, but you would only be 
selecting from rows for logins of the last 90 days instead of all rows.

--Ken



More information about the thelist mailing list