Selecting Primary Key by Picking the Highest Value-No Aggregate

[gicarto]

Hello-



This is my first post on this site and I don't really know how I would know what to look for in the existing forums so I apologize in advance. I am trying to write a query that will pick a record based on the highest value in a column but I am not having any luck. Here is the data I have and what I want it to look like after the query is run.

Here is the current table I am trying to change.

tid	municipality	number	year	value
37201_005	Abbotsford	5	2019	15,333,800
10201_005	Abbotsford	5	2019	586,100
37201_006	Abbotsford	6	2019	19,004,400
10201_006	Abbotsford	6	2019	4,771,600
10201_007	Abbotsford	7	2019	2,094,400

Here is how I want the final result to look like

tid	municipality	number	year	value
37201_005	Abbotsford	5	2019	15,333,800
37201_006	Abbotsford	6	2019	19,004,400
10201_007	Abbotsford	7	2019	2,094,400

These tables are for a taxing jurisdiction in a city that is located in 2 counties. I need to pick the record (with the primary key) for the district in the County with the highest value. For example, Abbotsford has two taxing districts in two counties (numbers 5 and 6). Tax jurisdiction #5 is valued at 15,333,800 in county 1 and 586,100 in county 2. Since it is in two counties, there are two primary keys. Tax jurisdiction has the primary keys of 37201_005 and 10201_005. Since 37201_005 is valued higher than 10201_005, I want my result set to only report 37201_005. Any help will be greatly appreciated.

[June7]

Try a TOP N query. Review http://allenbrowne.com/subquery-01.html#TopN

Not seeing a field for county identifier.

BTW, year and value are reserved words and should not use reserved words as names for anything.

[Gicu]

Have a look at the attached and please let us know if that is what you wanted (look at the qryFinalMaxData).

Cheers,

[gicarto]

I appreciate your help. I did not try the Top N query yet. I had success using Gicu's suggestion. Here is the full query that now works in my database:

1. Aggregate Query
SELECT M.muniName, T.tidNumber, V.currentYear, Max(V.curVal) AS MaxOfcurVal, YP.Type
FROM tblTifType AS YP INNER JOIN
(tblMunicipality AS M INNER JOIN
(tblTif AS T INNER JOIN tblTifValue AS V ON T.tid = V.tid) ON
M.mid = T.mid) ON YP.typeID = T.tidTypeId
GROUP BY M.muniName, T.tidNumber, V.currentYear, YP.Type, M.splitCo
HAVING (((V.currentYear)=2019) AND ((M.splitCo)="*"));




2. Select Record id with highest value and union with one-county district
SELECT T.tid, M.muniName, T.tidNumber
FROM (qselSplitTidAgg AS AG INNER JOIN
(tblTif AS T INNER JOIN tblTifValue AS V ON T.tid = V.tid) ON
(AG.tidNumber = T.tidNumber) AND
(AG.currentYear = V.currentYear) AND
(AG.MaxOfcurVal = V.curVal)) INNER JOIN
tblMunicipality AS M ON (M.mid = T.mid) AND
(AG.muniName = M.muniName)
GROUP BY T.tid, M.muniName, T.tidNumber
ORDER BY M.muniName
UNION
SELECT T2.tid, M2.muniName, T2.tidNumber
FROM (tblMunicipality AS M2 INNER JOIN
tblTif AS T2 ON M2.mid = T2.mid) INNER JOIN
tblTifValue AS V2 ON T2.tid = V2.tid
WHERE (((M2.splitCo) Is Null) AND
((T2.terminated)=No) AND
((V2.currentYear)=2019));