Filter out values that do not have max value

[bigchicagobob]

Hello,

I'm trying to create a list where I have a single value in column a [home], single value in column b [location] and single max value in column c rooms. For example, here is the raw data below.

I'd like to filter out the [house 4 - omega - 1] row because there is another house with a greater number of rooms. How would i write this in a query or two?

Home	location	Rooms
house 1	alpha	4
house 2	beta	3
house 3	delta	5
house 4	omega	1
house 4	epsilon	12
house 5	sigma	8

[JoeM]

One way:

1. First create an Aggregate Query which only has the Home and Rooms fields. Make it an Aggregate (Totals) Query, choosing "Maximum" for the Totals Row under the Rooms field. This will return a query of each House number with maximum number of rooms.

2. Create a new query, linking your original table to the query you just created above on both the Home and Rooms fields. Then return all three fields from the Table.

This should return what you want. Note, you could still get multiple rows for a single house number if there is a "tie" for maximum number of rooms for a single house.
For example, if there was another record "for house 4" with 12 rooms, both records with 12 rooms would be returned.