Return Max Date of a column independent of other columns

Question

I have two tables like this:

Client Table (let's name it like ClientTable)

ContactID	Name	Email
001	John	johnemail01@gmail.com
002	Maria	mariaemail02@gmail.com
003	Carlos	carlosemail03@gmail.com
004	Patricia	patriciaemail04@gmail.com

Travels Table (let's name it like TravelTable)

ContactID	TripDate	Country
001	2022-10-18	Germany
003	2022-02-05	Canada
001	2022-03-07	EUA
002	2022-07-02	India
004	2022-01-28	Austria
003	2022-01-28	Mexico

What I need is to catch John's latest trip (To Germany), regardless of what country he's been to. I wrote my SQL like this:

SELECT DISTINCT a.ContactID, a.Name, a.Email, MAX(b.TripDate) AS 'LastTrip', b.Country
FROM ClientTable a WITH (NOLOCK)
LEFT JOIN TravelTable b ON a.ContactID = b.ContactID
WHERE a.ContactID IS NOT NULL AND a.Email IS NOT NULL
GROUP BY a.ContactID, a.Name, a.Email, b.Country

The problem is that I getting this output:

| ContactID | Name | Email                 | LastTrip   | Country |
| --------- | ---- | --------------------- | ---------- | ------- |
| 001       | John | johnemail01@gmail.com | 2022-10-18 | Germany |
| 001       | John | johnemail01@gmail.com | 2022-03-07 | EUA     |

I really struggle with this one and don't get what I'm not doing to get just the last trip from John.

Thanks in advance.

[Bad habits: putting `nolock` everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — Dale K, Oct 20 '22 at 00:16
Thanks for the advice and for the article. It was quite enlightening. — Cauê Milliatti, Oct 20 '22 at 01:33

griv · Accepted Answer · 2022-10-20T00:41:04.267

You can use a subquery to get the MAX date:

SELECT 
  a.ContactID, 
  a.Name, 
  a.Email, 
  b.TripDate AS 'LastTrip', 
  b.Country
FROM ClientTable a WITH (NOLOCK)
LEFT JOIN TravelTable b ON a.ContactID = b.ContactID
WHERE a.ContactID IS NOT NULL 
  AND a.Email IS NOT NULL
  AND b.TripDate = (SELECT MAX(c.TripDate) 
                    FROM TravelTable c)

Result:

| ContactID | Name | Email                 | LastTrip   | Country |
|-----------|------|-----------------------|------------|---------|
| 1         | John | johnemail01@gmail.com | 2022-10-18 | Germany |

Fiddle here.

Return Max Date of a column independent of other columns

1 Answers1