I have a select statement that contains a dense_rank function written in Oracle but I'm having trouble figuring out how to make it compatible for postgressql (v11.x)
SELECT facility_id_fk, max(inspection_date) as last_inspection,
max(inspection_type) keep (dense_rank first order by inspection_date desc) as inspection_type
FROM facility_inspections
GROUP BY facility_id_fk
This query gives me the last inspection and what type that last inspection was:
-------------------------------------------------
facility id | inspection date | inspection type
-------------------------------------------------
93 04/28/2020 FULL
94 04/28/2020 LIMITED
-------------------------------------------------
I've tried the following but it leaves out the inspection type description which I need, and still provides duplicates and looking at the rank column, it looks like its ranking all inspection in the table and not for a specific facility
SELECT facility_id_fk, max(inspection_date) as last_inspection,
dense_rank () OVER (
PARTITION BY inspection_type
ORDER BY inspection_date DESC
) rank_order
FROM facility_inspections
GROUP BY facility_id_fk, inspection_date, inspection_type
In Postgres, you can do this with handy extension distinct on:
select distinct on (facility_id_fk) *
from facility_inspections
order by facility_id_fk, inspection_date desc
If you were to do this with window functions, which are much more portable than vendor specific syntaxes such as Oracle's keep or Postgres' distinct on, you could phrase the query as:
select *
from (
select f.*, row_number() over(partition by facility_id_fk order by inspection_date desc) rn
from facility_inspections f
) f
where rn = 1