0

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.

1
  • Provide complete tables CREATE TABLE scripts. Or at least specify table alias for EACH column in your query. Anycase - enumerate rows by sorting column in CTE and select only 1st in main query.
    – Akina
    Dec 3, 2020 at 4:27

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.