Extracting maximums

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone please tell me how to do the following:
E.G. field1 = RecipeName; field2 = RecipeVersion
E.G. field1 = Bamboo 10 M1; field 2 = 1
E.G. field1 = Bamboo 10 M1; field 2 = 2

How do I extract all records where RecipeVersion = maximum per field 1? The
maximum version number per recipe is different, so I can't just extract all
recipes where the version number = a certain number. It has to be the maximum
number for that specific recipe name.

Thx in advance...
 
OK... let me clarify a bit more, cos the solutions provided did not really
solve the problem:
E.G. field1 = RecipeName; field2 = RecipeVersion
rec 1 E.G. field1 = Bamboo 10 M1; field 2 = 1 ...
rec 2 E.G. field1 = Bamboo 10 M1; field 2 = 1 ...
rec 3 E.G. field1 = Bamboo 10 M1; field 2 = 1 ...
rec 4 E.G. field1 = Bamboo 10 M1; field 2 = 2 ...
rec 5 E.G. field1 = Bamboo 10 M1; field 2 = 2 ...
rec 6 E.G. field1 = Bamboo 10 M1; field 2 = 2 ...

Let's assume that version 2 is the last version for this specific recipe,
then I want to extract the last three records...
 
Dear Fox:

You should use a correlated subquery:

SELECT field1, field2
FROM YourTable T
WHERE field2 = (SELECT MAX(field2) FROM YourTable T1
WHERE T1.field1 = T.field1)

This says:

Display field1 and field2 from your table but limit it to those rows where
field2 has the largest value of all rows for that value of field1.

After doing this for years, it practically says that to me just looking at
the code.

The keys to understanding how this works are:

- the use of aliasing (essential when working with 2 copies of the same
table)
- subqueries (the thing in the last 2 lines inside parens)
- correlation (the overall principle being used here, where the subquery
correlates to the keyed field1)

Is this at all clear? Does it work for you?

Tom Ellison
 
I understand completely what you're trying to tell me, and I've tried it...
but it didn't do what I wanted it to do. I'm including the whole Access query
SQL string:

SELECT T.ShadeRecName, T.ShadeRecID, T.ShadeRecVer, STEPS.StepOrder,
STEPS.StepType
FROM SHADE_RECIPES AS T LEFT OUTER JOIN STEPS ON T.MethodID = STEPS.MethodID
WHERE (((T.ShadeRecVer)=(SELECT MAX(T.ShadeRecVer) FROM SHADE_RECIPES T1
WHERE T1.ShadeRecVer = T.ShadeRecVer)))
ORDER BY T.ShadeRecName, T.ShadeRecVer;
 
Back
Top