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;
Advertisement
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;