Help with SQL Syntax - return all records of last date visited

Without using TOP or ROW_NUMBER()  (because each person has a different record amount of times they visited) how can I return the last date visited.

As of today July 09, 2009 , I would to look back at the last visited date and return all rows of records for their last date of visit.

VisitKey       Date of Service
    11            07/01/2009
    12            07/01/2009
    13            07/01/2009
    14            07/01/2009
    15            07/05/2009
    16            07/05/2009
    17            07/05/2009
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.

Kevin CrossChief Technology OfficerCommented:
Why would ROW_NUMBER() not work for you here?  Doesn't matter the number of records, if you ORDER BY date_of_service DESC and take row = 1 then will always be the last visit whether a person has one or a thousand visits.
Kevin CrossChief Technology OfficerCommented:
If a person is represented by a visit key then would look something like this (can replace visit key with proper column for person id to get per person last date of service).
-- without row_number()
SELECT visitkey, max(date_of_service) as lastVisitDate
FROM your_table_name
GROUP BY visitkey;
-- with row_number()
SELECT visitkey, date_of_service as lastVisitDate
   SELECT *, row_number() OVER(PARTITION BY visitkey ORDER BY date_of_service DESC) AS row
   FROM your_table_name
) a
WHERE a.row = 1;

Open in new window

If your date of service does not have the time (datetime field with 00:00:00 time)....

select * from SomeTable
where Date_Of_Service = (select max(Date_Of_Service) from SomeTable)

Open in new window


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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

It would be nice to see the table structure but letss assume you have a Person_ID, VistitKey, and DateVisited as your columns. To get all records for each person for thier last visit data you can do something like this:
Select  t.*
From   YourTable t INNER JOIN (Select Person_ID, MAX(DateVisited) From YourTable Group by Person_ID) l
             ON t.Person_ID = l.Person_ID
After reading the question again, I believe I misread the requirement.
Kevin CrossChief Technology OfficerCommented:
And I see now why this didn't make sense to me about row_number().  You want to pull back rows of data, the sample you gave is not finished product... :( I need to read better.

I would do this like Brandon showed or using INNER JOIN; however, slight syntax correction to what P. posted.
FROM your_table_name t
INNER JOIN (SELECT Person_ID, max(Date_Of_Service) AS max_DOS FROM your_table_name GROUP BY Person_ID) l
   ON t.Person_ID = l.Person_ID AND t.Date_Of_Service = l.max_DOS

Open in new window

Kevin CrossChief Technology OfficerCommented:
LOL, it happens!
I still misread what you wrote Brandon.  Thought you nailed it.  Think you are just missing the link on user.
select * 
from SomeTable t1
where Date_Of_Service = (select max(Date_Of_Service) from SomeTable t2 where t2.person_id = t1.person_id)

Open in new window

you can always use dense_rank() instead of row_number().  Dense_Rank will return the same number for duplicate values.

Yes.  That is what I was missing.  I used a subselect instead of a correlated subselect.
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 2005

From novice to tech pro — start learning today.