All we need is an easy explanation of the problem, so here it is.
I would like to extract all the rows from a table until and including
check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don’t have a lower bound for
checked_at, the lower bound could be the
checked_at value of the first row which has the type
scheduled, but I don’t see how I can get that.
CREATE TABLE project_check_history ( id serial PRIMARY KEY, project_id int4, check_type varchar, checked_at timestamp ); INSERT INTO project_check_history(project_id, check_type, checked_at) VALUES (1, 'instant', '2016-09-26'), (1, 'instant', '2016-09-25'), (1, 'scheduled', '2016-09-24'), (1, 'instant', '2016-09-23'), (1, 'scheduled', '2016-09-23'); (1, 'instant', '2016-09-22'), (1, 'scheduled', '2016-09-21');
This is part of a bigger question here, but I’m really stuck, and don’t see how I can do it.
Expected output it will be:
checked_at 2016-09-26 2016-09-25 2016-09-24
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This will do what you want. If there is no row with
check_type = 'scheduled' in the specified interval, it will return no rows at all. If you want all the rows instead, it will have to be slightly modified:
WITH find_schedule AS ( SELECT created_at FROM project_check_history WHERE check_type = 'scheduled' AND checked_at <= '2016-09-26' ORDER BY checked_at DESC LIMIT 1 ) SELECT -- DISTINCT created_at FROM project_check_history WHERE checked_at <= '2016-09-26' AND checked_at >= (TABLE first_schedule) ;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂