We help IT Professionals succeed at work.

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

Say55ow
Say55ow asked
on
Medium Priority
277 Views
Last Modified: 2012-06-27
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.

EXAMPLE:
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
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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 Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
FROM (
   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

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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
P.
After reading the question again, I believe I misread the requirement.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
mwvisa1:

Yes.  That is what I was missing.  I used a subselect instead of a correlated subselect.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.