Skip to content
Advertisement

How to fetch the the second row

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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement