Filtering old Revisions out of a query

[MrBologna]

I have a query that pulls a list of all assemblies & their parts from an ODBC database.
The tables are formatted like this:

Assemblies: AssemblyRecordNumber, Assembly_No, Assembly_Description


BOMItems: RecordNumber, AssemblyRecordNo (joined with assemblies), RevisionNumber, ComponentRecordNumber (joined with parts database))

So there is one record for each part for each assembly (quite a few records).

It does its job pretty well, except for one special case:
it does not differentiate between assembly revisions.

So say I have assembly A, revision 0 with 10 parts.
Then that assembly gets updated to have 5 parts (removed 5 of the parts from the assembly) and becomes revision 1.
Right now, the query does not differentiate between the two revisions, and groups like rows to return 10 parts. Obviously I can turn off the grouping on revision, but I don't want to get 15 parts.

Is there a good way to filter out everything except the rows with a maximum value in the revision number field, where the assembly number is the same?

The only way I can think of right now is by creating a temp table of each assembly's current revision number each time I need to use this query and checking against that, but this is not a great solution because these tables are very large (lots of assemblies with lots of components each) and I would like to optimize as much as is easily possible.

[CJ_London]

join your query to another won that has the max version in it. Something like

Q2:
SELECT Assembly, max(revision) as maxRev
FROM myTable
GROUP BY Assembly

your query simplified since I don't know what it looks like
SELECT Assembly, Revision, PartNo
FROM myTable

your query incorporating Q2

SELECT Assembly, Revision, PartNo
FROM myTable INNER JOIN Q2 ON myTable.Assembly=Q2.Assembly AND myTable.Revision=Q2.MaxRev

[MrBologna]

Awesome, got it working and the second query is only a minor increase in run time. Thanks!