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) 
      (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:

  • @ypercubeᵀᴹ the rows will be in descending order of the checked_at – alexsmn Sep 26 '16 at 18:55
  • @ypercubeᵀᴹ using that data set the result will not include instant, 2016-09-23 it will stop at the first occurrence of schedule, and in this case it will return the checked_at for the first 3 rows – alexsmn Sep 26 '16 at 18:58
  • @ypercubeᵀᴹ then it will continue till scheduled 2016-09-23 and return 2016-09-26, 2016-09-25, 2016-09-23, no just once, but will not matter anyway, because this will be part of another condition that will pick rows where a date IN this set of dates, and thank you – alexsmn Sep 26 '16 at 19:00

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
FROM project_check_history
WHERE checked_at <= '2016-09-26'
  AND checked_at >= (TABLE first_schedule) ;  
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.