Reputation: 1251
I have a dataset (query in this case) that kind of looks like this:
ZipCode Territory Date
----------------------------------
12345 Unknown 9/30/2015
12345 Unknown 9/25/2015
12345 Istanbul 9/20/2015
12345 Istanbul 9/10/2015
12345 Unknown 9/5/2015
12345 Istanbul 8/31/2015
12345 Istanbul 8/21/2015
12345 Unknown 8/16/2015
12345 Constantinople 8/11/2015
12345 Constantinople 8/1/2015
12345 Constantinople 7/22/2015
12345 Constantinople 7/12/2015
I'd like to return the max date and KNOWN territory name for each zip code (one row per zipcode). The end result I would expect from the previous query would turn into this:
ZipCode Territory Date
----------------------------------
12345 Istanbul 9/20/2015
My current best efforts only got me here:
ZipCode Territory Date
---------------------------------
12345 Istanbul 9/20/2015
12345 Constantinople 8/11/2015
Help! This is a huge gap in my SQL writing abilities.
Upvotes: 0
Views: 4820
Reputation: 272446
This is essentially a greatest-n-per-group question. Group your data by zip code to grab the latest date per zip code. Then match the zip code-latest date pairs with your data:
SELECT YourTable.*
FROM YourTable
INNER JOIN (
SELECT ZipCode, MAX(Date) AS Date_Max
FROM YourTable
WHERE Territory <> 'Unknown'
GROUP BY ZipCode
) AS TempGroup ON
YourTable.ZipCode = TempGroup.ZipCode
AND YourTable.Date = TempGroup.Date_Max
Result (tested in MS-Access 2007):
ZipCode Territory Date
----------------------------------
12345 Istanbul 9/20/2015
Upvotes: 1
Reputation: 2302
You need two queries.
The first to retrieve the max date.
SELECT Zipcode, Max([Date]) AS MaxDate FROM <TableName> Group BY Zipcode
Let's save this query as qryMaxDateForZipCode
The second to query the territory for that date.
SELECT ZipCode, Territory FROM qryMaxDateForZipCode
INNER JOIN <TableName> ON qryMaxDateForZipCode.MaxDate = <TableName>.[Date]
However you may have two territories qualifying for the max date. What do you want to do then?
Upvotes: 2
Reputation: 73
This might help (?):
Select ZipCode, MAX(Territory), Max([Date]) AS [Date]
Group By ZipCode
Upvotes: 0