CODEWITHSUNDEEP
sqlms-accessms-access-2010
ChrisG
ChrisG

Reputation: 1251

Select a value based on the aggregate value of another column

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

Answers (3)

Salman Arshad
Salman Arshad

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

E Mett
E Mett

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

Viandry
Viandry

Reputation: 73

This might help (?): 

Select ZipCode, MAX(Territory), Max([Date]) AS [Date]
Group By ZipCode

Upvotes: 0

Related Questions