Select full record with minimum value for column grouped by joined value

Question

I have a table images with various columns, and two join tables images_units and images_sites that contains pairs of ids (image_id, unit_id), (image_id, site_id). One of the columns in the images table is sort.

I want to return each distinct record from the images table that has the minimum value of sort for each unit_id, with all records returned matching a given site_id. (for every unit_id there will be multiple image records.)

I thought I would be able to do something like this:

select images.*, unit_id from images 
join images_units on images.id = images_units.image_id 
join images_sites on images.id = images_sites.image_id 
join (select min(sort) as minSort, unit_idas matchID from images join images_units on images.id=images_units.image_id group by matchID) as sq 
where images.sort = minSort and a.unit_id = matchID and site_id = xyz;

In order to get all the values that I need, but I'm getting a syntax error near where so I presume I can't use it like this.

I ultimately want to return distinct rows and only the columns from images and nothing else (i.e the columns 'select distinct images.* from images' would give) but I think I should just be able to select those from the results of the query I've given - I'm including this note in case it's relevant to an alternative solution.

Answer 1

you are missing on {condition} for your last join : however I'm not sure

select images.*, unit_id 
from images 
join images_units 
    on images.id = images_units.image_id 
join images_sites 
    on images.id = images_sites.image_id 
join (
    select min(sort) as minSort, unit_id as matchID 
    from images 
    join images_units 
        on images.id=images_units.image_id 
    group by matchID
    ) as sq 
    on sq.matchId = ??  -- <-- here add the proper join condition
where 
images.sort = minSort 
and a.unit_id = matchID 
and site_id = xyz;