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

aggregatepostgresql

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.

Best Answer

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;

Related Solutions

Postgresql – Is it possible to select distinct values in a json document using PostgreSQL

Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job:

SELECT DISTINCT ON (doc->'s') doc
FROM  (
   SELECT '[
    {
      "id":1,
      "s":"foo"
    },
    {
      "id":2,
      "s":"bar"
    },
    {
      "id":3,
      "s":"foo"
    }]'::jsonb AS j
   ) t
   , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER  BY doc->'s', rn;

Or, unless s is a nested object, it's probably cheaper to fold on the text value instead of the jsonb (sub-)record. Just use the operator ->> instead of -> in this case. The result is the same:

 doc
----------------------
'{"s": "bar", "id": 2}'
'{"s": "foo", "id": 1}'

Replace the subquery t with your actual table.

Key elements are jsonb_array_elements() (or json_array_elements()) in a LATERAL join with WITH ORDINALITY and then the Postgres-specific DISTINCT ON.

Related, with more explanation:

Postgresql – Avoid joining to the same table multiple times

Try how good this works for you

Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1

Best Answer

Related Solutions

Postgresql – Is it possible to select distinct values in a json document using PostgreSQL

Postgresql – Avoid joining to the same table multiple times

Related Question