Accessing previous Login time based on Customer Name or ID in SQL

Question

I have a log file as shown in the table. Every time the user logs in, his details are updated in this database.

I want to select the previous ActivityDate of that particular user when ever he logs in.

For example, in the Table Tom logs in on Aug 9 2015, I want to show the last sign in (ActivityDate) of Tom which is Aug 3 2015. The table gets updated whenever a user logs in. Can any one help how to get the info ?

UserID  UserName    Activity    ActivityDate
1001    Bill        SignIn      Aug 1 2015
1002    Tom         SignIn      Aug 3 2015
1003    John        SignIn      Aug 3 2015
1001    Bill        SignIn      Aug 4 2015
1001    Bill        SignIn      Aug 6 2015
1002    Tom         SignIn      Aug 9 2015

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Looks like a standard windowing function that's been asked a bunch of times here and many other places. For example: https://stackoverflow.com/a/16777/5074786

In your case, assuming the insert happens before you display the last sign-in, just order by ActivityDate and return the second row (i.e. rownum=2). If you want to pull the data before inserting the current login entry, then just SELECT TOP 1 * FROM Table WHERE UserName='Tom' and you're done.

score 0 · Answer 2 · answered Aug 13 '15 at 18:01

select ActivityDate from logs
where UserId = 1002
order by ActivityDate desc
limit 1 offset 1

The above query assumes two things

You are using mysql
You are entering the log as soon as the user logs in.(Basically checking the database after log has been update).

Accessing previous Login time based on Customer Name or ID in SQL

2 Answers2