• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Need to get a COUNT plus a most recent date

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!
0
saoirse1916
Asked:
saoirse1916
1 Solution
 
Patrick MatthewsCommented:
select userFName, userLName, count(*) as loginCount, max(sessionModifiedDate) as LastLogin
from tblUsers, tblSessions
where tblSessions.userID = tblUsers.userID
group by tblSessions.userID, userFName, userLName
order by loginCount desc, userLName asc
0
 
saoirse1916Author Commented:
Sweet...thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now