I have a query below which is used to make a view. This query pulls the latest record of the report_id
.
One report_id can have multiple unique report_info_id
SELECT DISTINCT ON (t.report_id) t.temporal_start_date, t.report_id, t.report_info_id, t.status FROM reports_infos t ORDER BY t.report_id, t.temporal_start_date DESC;
This results in data something like
temporal_start_date |report_id|report_info_id|status | -----------------------|---------|--------------|-----------------------| 2006-09-22 00:00:00| 49| 20435|Validated | 2006-08-02 00:00:00| 89| 25782|Validated | 2006-11-13 00:00:00| 108| 20436|Validated | 2020-12-09 09:27:29.214| 130| 31755|Edited after validation|
Lets take the example of last record where report_id= 130
select report_info_id , report_id , temporal_start_date, temporal_end_date,status from reports_infos where report_id=130 order by temporal_start_date desc
report_info_id|report_id|temporal_start_date |temporal_end_date |status | --------------|---------|-----------------------|-----------------------|-----------------------| 31755| 130|2020-12-09 09:27:29.214| |Edited after validation| 29714| 130|2020-11-20 14:50:44.227|2020-12-09 09:27:29.215|Validated | 29713| 130|2020-11-20 14:49:27.088|2020-11-20 14:50:44.228|Edited after validation| 25788| 130| 2006-03-20 00:00:00|2020-11-20 14:49:27.089|Validated |
Now it is required that in first query I want such data if the status is “Edited after validation” then pull the second row of that report_id
Expected result
temporal_start_date |report_id|report_info_id|status | -----------------------|---------|--------------|-----------------------| 2006-09-22 00:00:00| 49| 20435|Validated | 2006-08-02 00:00:00| 89| 25782|Validated | 2006-11-13 00:00:00| 108| 20436|Validated | 2020-11-20 14:50:44.227| 130| 29714|Validated|
Advertisement
Answer
Based on your description of the problem you need not the second row in each group as such, but a row that satisfies an additional criteria. You want to skip the row with status “Edited after validation”. To do that you can simply add a WHERE
filter to your query:
SELECT DISTINCT ON (t.report_id) t.temporal_start_date, t.report_id, t.report_info_id, t.status FROM reports_infos t WHERE t.status <> 'Edited after validation' ORDER BY t.report_id, t.temporal_start_date DESC;
or
SELECT DISTINCT ON (t.report_id) t.temporal_start_date, t.report_id, t.report_info_id, t.status FROM reports_infos t WHERE t.status = 'Validated' ORDER BY t.report_id, t.temporal_start_date DESC;
Both queries produce the same result if status
can be only either Edited after validation
or Validated
. If status
can have some other values as well, pick the variant that suits the necessary logic.