Extract all rows until and including

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply