Query: filter fields on max number

[Mikey1987]

Hello everyone,

I'm sorry to ask sth that must be terribly easy for all of you, but I can't figure it out
I have a table with two numberfields (let's call it NumbersToFilter and FilteringField)

I want a query that shows me ALL NumbersToFilter data WHERE FilteringField is the highest

Eg. if this is my table:
NumberstoFilter / FilteringField
999 / 50


947 / 44
920 / 50
941 / 48

I want my query to return 999 and 920 (filteringfield max = 50)

I'm currently using an aggregate query, using group by NumberstoFilter and max FilteringField, but it returns all the fields (the maximum is simply on top )

Any help?

Thanks a lot,
Mikey

[JoeM]

You want to use an Aggregate Query to return just the maximum values of the FilteringField (and nothing else).
Then you can either create a new query based on your original table and this aggregate query, joining on the FilteringField field and returning the NumberstoFilter field,
or you can use this aggregate query in a nested query to do the same thing in a single query.

Here is what the SQL code structure of the nested query would look like:

SELECT NumberstoFilter
FROM MyTable
WHERE FilteringField in
(SELECT Max(MyTable.FilteringField)
FROM MyTable);

[Mikey1987]

You want to use an Aggregate Query to return just the maximum values of the FilteringField (and nothing else).
Then you can either create a new query based on your original table and this aggregate query, joining on the FilteringField field and returning the NumberstoFilter field,
or you can use this aggregate query in a nested query to do the same thing in a single query.

Here is what the SQL code structure of the nested query would look like:

SELECT NumberstoFilter
FROM MyTable
WHERE FilteringField in
(SELECT Max(MyTable.FilteringField)
FROM MyTable);

Works like a charm
u da best!