postgres: dynamic subquery in join filtering

Question

Currently trying to define a dynamic query in Postgres. Essentially the filtering done by a subquery used in the Join depends on the results for each row. The idea is that each “name” in the query will only return one project_id based on a specific set of parameters.

The basic query without any filtering:

SELECT a.name, rproj.raproject_id, rproj.current_status, rproj.create_date
FROM focalpoint fp
JOIN agl_asset a ON a.serial_number::text = fp."RoutineNumber"::text
JOIN agl_raproject AS rproj ON a.asset_id = rproj.asset_id 
JOIN agl_auditproject AS audit ON rproj.auditproject_id = audit.auditproject_id
ORDER BY a.name

That would return:

name    raproject_id        current_status      create_date
AssetA  405323966463427000  Review              24/10/2014 18:35
AssetA  405323966463460000  Review              07/10/2016 14:04
AssetA  405323966463413000  Risk Identification 28/11/2013 14:16
AssetA  405323966463413000  Closed              21/11/2013 17:33
AssetB  405323966463412000  Monitoring          15/11/2013 11:26
AssetB  405323966463427000  Review              24/10/2014 18:35
AssetB  405323966463461000  Assessment          13/10/2016 10:32
AssetB  405323966463412000  Closed              15/11/2013 11:44

But I only want one “project” per asset. If I was just trying to get the “newest” based on the create_date it would be:

SELECT a.name, rproj.raproject_id, rproj.current_status, rproj.create_date
FROM focalpoint fp
JOIN agl_asset a ON a.serial_number::text = fp."RoutineNumber"::text
JOIN agl_raproject AS rproj ON a.asset_id = rproj.asset_id AND rproj.create_date = ((SELECT max(rproj2.create_date) AS max
 FROM agl_raproject rproj2
 JOIN agl_auditproject audit ON rproj2.auditproject_id = audit.auditproject_id
 WHERE a.asset_id = rproj2.asset_id AND audit.project_type::text = 'ngERMAssessment'::text))
JOIN agl_auditproject AS audit ON rproj.auditproject_id = audit.auditproject_id
ORDER BY a.name

But what I need is:

If there’s any project, for this specific asset, where the current_status is “Monitoring” – Return that row
If not, take the newest one (as I’ve done already on the last query).

But again, only one project from raproject should be returned per asset.

EDIT: The expected return would be:

name    raproject_id        current_status      create_date
AssetA  405323966463460000  Review              07/10/2016 14:04
AssetB  405323966463412000  Monitoring          15/11/2013 11:26

Clodoaldo Neto · Accepted Answer · 2017-01-17 12:43:05Z

1

distinct on

select distinct on (a.name)
    a.name, rproj.raproject_id, rproj.current_status, rproj.create_date
from
    focalpoint fp
    inner join
    agl_asset a on a.serial_number::text = fp."RoutineNumber"::text
    inner join
    agl_raproject as rproj on a.asset_id = rproj.asset_id 
    inner join
    agl_auditproject as audit on rproj.auditproject_id = audit.auditproject_id
order by a.name, rproj.current_status <> 'Monitoring', rproj.create_date desc

Check the order by requirement

edited Jan 17, 2017 at 12:43

answered Jan 17, 2017 at 12:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres: dynamic subquery in join filtering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related