Simple SQL query question (group by clause)
Posted on 2014-08-21
I have a table in SQLite that looks like this:
ID COMPUTERNAME USERNAME ACTION ACTIONDATE
1 COMPANYPC1 john login Jan 1 2014 8:06am
2 COMPANYPC1 john logout Jan 1 2014 8:24am
3 COMPANYPC2 mary login Jan 4 2014 9:15am
4 COMPANYPC3 mary login Jan 6 2014 9:24am
5 COMPANYPC2 john login Jan 7 2014 12:33pm
Basically it's a log of what user has logged in/out of what PC, at what time. It's automatically populated by various scripts and group policies on the network.
I need to determine a list of Most recently used computers
So that's easy, I do something like this:
SELECT computername, max(actiondate) as last_login_date
GROUP BY computername
ORDER BY last_login_date desc
That nicely gives me a list of computers, and by using the "max()" aggregate clause I can get the most recent "date" that it was logged in.
But... how can I obtain the USERNAME and ACTION?
I want something like this:
COMPUTERNAME LAST_ACTION_DATE ACTION BY_WHOM
COMPANYPC1 Jan 7 2014 12:06pm login mary
COMPANYPC2 Jan 3 2014 08:20am logout john