We help IT Professionals succeed at work.

Need to get a COUNT plus a most recent date

Medium Priority
439 Views
Last Modified: 2012-05-06
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!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Sweet...thanks!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.