maximum query

[Beans]

I have a table of mapunits joined to a table of components. There can be
many components in each mapunit. In the components table there is a
percentage field and a t factor field. For each mapunit, I would like to
query the components table to return the component with the highest
percentage of the and its associated t factor. I use 'totals' to return the
max of the percentage field but it will return all components of the mapunit
if I don't I don't put a condition of the t factor field.

Once I have the max percentage, how can I get its t factor.

 

[Ken Sheridan]

You can join the tables and then restrict the result by means of a subquery
which identifies the MAX percentage value per mapunit, e.g.

SELECT Mapunits.mapunit, component, [t factor]
FROM Mapunits INNER JOIN Components AS C1
On C1.mapunit = Mapunits.mapunit
WHERE percentage =
(SELECT MAX(percentage)
FROM components AS C2
WHERE C2.mapunit = C1.mapunit);

Or you can do it in stages:

1. Create a query which returns the mapunit, and MAX(percentage) from the
Components table.

2. Create another query which joins the Mapunits to the Components tables
on mapunit, and the Components table to the query on the mapunit and
percentage columns, and return the mapunit, component and t factor columns.

Ken Sheridan
Stafford, England

 

[Marshall Barton]

I have a table of mapunits joined to a table of components. There can be
many components in each mapunit. In the components table there is a
percentage field and a t factor field. For each mapunit, I would like to
query the components table to return the component with the highest
percentage of the and its associated t factor. I use 'totals' to return the
max of the percentage field but it will return all components of the mapunit
if I don't I don't put a condition of the t factor field.

Once I have the max percentage, how can I get its t factor.

If you want to get the t factors from only the records with
the max percentage, then you need to use a subquery. I
think this may be something like what you want:

SELECT M.mapunit, C.component, C.t, C.percentage
FROM mapunits As M INNER JOIN components As C
ON {however the tables are joined}
WHERE C.percentage = (SELECT Max(X.percentage)
FROM components As X
WHERE {however the tables are joined})