Need to get a COUNT plus a most recent date
Posted on 2009-02-09
I've got a query that collects all the logins to my application grouped by user, which works great. I would like to add in the most recent date to the query so that I don't have to run two connections to the database. Here's what I've got at the moment:
select userFName, userLName, count(*) as loginCount
from tblUsers, tblSessions
where tblSessions.userID = tblUsers.userID
group by tblSessions.userID, userFName, userLName
order by loginCount desc, userLName asc
If I simply add in the field that I'd like to grab (sessionModifiedDate), it of course errors out that it is not contained in the GROUP BY clause. And putting it in the GROUP BY clause then gives me a ton of records, each with a loginCount value of 1. Can someone point me in the right direction?
Thanks very much!