ms access, need to get all rows with a distinct column

I have a table called “parts” which stores information on electrical connectors including contacts, backshells etc. all parts that are part of an assembly have a value in a column called “assemblyID”. There is also a column called “partDefID”, in this column connectors will have a value of 2, contacts will be 3. I need to get rows for all connectors that have a unique assemblyID. It’s easy to get rows that represent connectors just by selecting rows with a partDefID of 2 but this will return multiple rows of connectors that may be part of the same assembly. I need only those rows of connectors with a unique assemblyID. How can I do this? see image below: access screenshot

what I am trying to get is just ONE of the rows shown below, any one of them would be fine as they are all part of the same assembly. just one of these rows needed

[update] it seems my question was not well formed and the use of images is frowned upon. Inserting a text version of my table looked REALLY horrible though! I’ll try to do better. yes, I’m a newb at both sql AND this website

Answer

If you want just one “connector” row per assembly ID, you can filter with a subquery. Assuming that PartRefID is a unique key:

select *
from parts as p
where [PartRefID] = (
    select max(p1.[PartRefID])
    from parts as p1
    where p1.[AssemblyID] = p.[AssemblyID] and p1.[PartDefID] = 2
)

Advertisement

Answer