Teradata most recent date

Hi All,
    I am trying to pull information from a table that has multiple dates.  Let's say it was a table tracking what days people went to the gym.  Every time the person scans his or her card, another row of data is created.  Let's say I only wanted to pull in the most recent visit for each gym member and the field name for the date is contact_date.  

I essentially want something like:

select Name, Contact_Date, Address from  TABLE1
where contact_date = most recent date of all the visits that gym member has.

I need the code to tell teradata to pull in the most recent date.
Who is Participating?
SharathConnect With a Mentor Data EngineerCommented:
You can try any of these queries.

select Name, Contact_Date, Address from  TABLE1 as t1
where contact_date = (select max(t2.contact_date) from TABLE1 as t2 where t1.Name = t2.Name)


select t1.Name, t1.Contact_Date, t1.Address from  TABLE1 as t1
JOIN (SELECT Name,max(Contact_Date) as Contact_Date FROM TABLE1 GROUP BY Name) as t2
ON t1.Name = t2.Name and t1.contact_date = t2.contact_date
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.

All Courses

From novice to tech pro — start learning today.