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.