Skip to content
Advertisement

How to get first row of data for each month (postgres)

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement