I am a beginner in postgres and would like to get the first row for each month (group by) but I’m not really sure how.
My table order
is as per below:
order_id | cust_id | order_date ------------------------------------------------ order1 | cust1 | January 19, 2020, 1:00 PM order2 | cust1 | January 30, 2020, 2:00 PM order3 | cust1 | February 20, 2020, 3:00 PM order4 | cust1 | February 28, 2020, 4:00 PM order5 | cust2 | February 27, 2020, 4:00 PM
the expected outcome should be as per:
order_id | cust_id | order_date ------------------------------------------------ order1 | cust1 | January 19, 2020, 1:00 PM order3 | cust1 | February 20, 2020, 3:00 PM order5 | cust2 | February 27, 2020, 4:00 PM
But I was not able to get the above result using the query below where the result I get is the same as the table:
select distinct on (order_date)cust_id, order_date, order_id from order group by delivery_date, customer_id, delivery_id order by delivery_date asc
Advertisement
Answer
Close. Use the month and get rid of the group by
:
select distinct on (cust_id, date_trunc('month', order_date) ) cust_id, order_date, order_id from order order by cust_id, date_trunc('month', order_date), delivery_date asc