SQL Query to Select Min and Max Values For Each Day Over a Period

I would like to select all the rows that contain either min or max datetime values for each equipment_id, for every day included in the period.

The code below selects the min and max datetime values for each equipment_id over the entire period. What can/should I change to reach desired select results?

Note: the values enclosed in braces in the code snippet represent dynamic values.

select *
from equipment
where created_at in (select min(created_at)
                     from equipment
                     where created_at >= {start_datetime} and created_at < {end_datetime}
                     group by equipment_id
                    ) or
      created_at in (select max(created_at)
                     from equipment
                     where created_at >= {start_datetime} and created_at < {end_datetime}
                     group by equipment_id
                    )
order by account, equipment_id, created_at asc;

Answer

Use window functions:

select e.*
from (select e.*,
             min(e.created_at) over (partition by e.equipment_id, to_date(e.created_at)) as min_ca,
             max(e.created_at) over (partition by e.equipment_id, to_date(e.created_at)) as max_ca
      from equipment e
     ) e
where e.created_at in (min_ca, max_ca)
order by account, equipment_id, created_at asc;

Advertisement

Answer