How do you limit data to just TOP RANKED rows

[abq]

Hello, I was trying to get only the TOP RANKED rows from a table like this, using a query.

Here is my data:

The result would be a table showing only the yellow shaded rows, which are those where the "fruit count" is the highest.
I tried with a "group by max", but as expected I do not get what I am looking for.
E.G. I get "London Pear 9" because Pear is max value and 9 is max value.
Similarly I get wrong results if I try to "group by first", because the data in the original table are not ordered.

Additionally, in case of ties for the first position, I would like to get the fruit value that comes first in a separate table like the following.
9 Pear
8 Orange
7 Banana
6 Apple
5 Kiwi
4 Mango
3 Peach

So for example correct results would be
Budapest Pear 7 (because Pear is higher than Peach)
and
Paris Banana 19 (because Banana is higher than Apple)

I am assuming you would need to run a couple layers of query for the result, but I am not finding the solution.
I attached an excel file with the basic data for this question.
Thanks for helping out!

-abq-

 

[pbaldy]

See if this works for you:

http://allenbrowne.com/subquery-01.html#TopN

 

[abq]

Hi and thank you for the quick reply.
This works, though require some tweaking in the SQL language box.
Is there any way I can obtain this result through the MS Access GUI interface?
I am currently on Access 11.

-abq-

 

[pbaldy]

You might be able to create the subquery SQL in the GUI, then copy it to the criteria in the main query's GUI.

 

[plog]

You can do this just by SQL. You want to find a specific record by finding the max value in 2 fields, this will require a few subqueries. Here's the first:

SELECT City, YourTableName.Fruit, FruitCount, Rank AS Rank
FROM YourFruitTableName
INNER JOIN YourRankTableName On YourRankTableName.Fruit = YourFruitTableName.Fruit

Paste that into a query and name it TopFruit_sub1. Replace the table names with the ones you actually have in your database. It just brings all the data you are going to need together into one object.

SELECT City, MAX(FruitCount) AS TopFruitCount
FROM TopFruit_sub1
GROUP BY City

Paste that into a query and name it TopFruit_sub2. It identifies the highest Fruit count for each city. Then use this sql:

SELECT TopFruit_sub1.City, MAX(Rank) AS TopRank, TopFruitCount
FROM TopFruit_sub1
INNER TopFruit_sub2 ON TopFruit_sub2.City = TopFruit_sub1.City AND ToFruit_sub2.TopFruitCount = TopFruit_sub1.FruitCount
GROUP BY City, MaxFruitCount

Paste that into a query and name it TopFruit_sub3. It breaks any ties within FruitCounts by using the highest rank value. Finally, the below query will give you the data you want:

SELECT TopFruit_sub1.City, TopFruit_sub1.Fruit, TopFruit_sub1.FruitCount
FROM TopFruit_sub1
INNER JOIN TopFruit_sub3 ON TopFruit_sub3.City = TopFruit_sub1.City
   AND TopFruit_sub3.TopRank = TopFruit_sub1.Rank
   AND TopFruit_sub3.TopFruitCount = TopFruit_sub1.TopFruitCount

 

[abq]

plog, thank you for your answer!
Your way is actually what I was looking for, as you can create all these queries by just using the GUI and not write any sql, which is a secondary but no less important objective of my exercise
ADDITIONALLY, by using this group of sub-queries, the result comes WAY much faster than adding the "TOP X" statement in the SQL language, when dealing with large tables.

I wonder if you could just add a calculated field with a "smart counter" that resets the count every time you hit a new City, then just filter by it?
That way I would only need to worry about ordering the table the way I need it, and then filter by the calculated field (a counter that restarts from 1 every time the list shows a new City).

-abq-

 

[plog]

Yes you could do that (search this forum for 'Running Sum Query' or 'Running Total Query'). But I believe that mehtod will be slower than a series of sub-queries--since you are calculating the position of every element.