help with sql to grab all users last visit date

I have two tables:

Accounts
LogOfVisits

I want to select all of my accountIDs from the table Accounts and get their last visit date (from the table LogOfVisits). Right now my query selects ALL of each accounts visit, I only want the latest one.

My query so far:

select distinct( acc.AccountID ), lv.LastDate
FROM Accounts acc
	left outer join LogOfVisits lv on acc.AccountID = lv.AccountID
where acc.adminID = 188 and
	  sa.SiteID = 826906 
order by acc.AccountID

Open in new window


I'm using left outer join because there are other tables I'm joining, but for sake of simplicity, I left them out
LVL 34
Big MontySenior Web Developer / CEO of ExchangeTree.org Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Group By with the MAX aggregate should do what you need:

select acc.AccountID, MAX(lv.LastDate)
FROM Accounts acc
	left outer join LogOfVisits lv on acc.AccountID = lv.AccountID
where acc.adminID = 188 and
	  sa.SiteID = 826906 
group by acc.AccountID
order by acc.AccountID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big MontySenior Web Developer / CEO of ExchangeTree.org Author Commented:
worked beautifully, thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.