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

Question

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

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — astentx, Dec 08 '20 at 16:03
@astentx i couldn't get the expected result with the linked thread. it also gives me another extra column which i wish to avoid. but it's very similar. thanks — tiredqa_18, Dec 09 '20 at 02:25
If you do not need some columns you can exclude them from select list. Use the column names you need. What about `result is not as I expected`: better to describe the exact problem, not general "not working". — astentx, Dec 10 '20 at 07:36

score 1 · Accepted Answer · answered Dec 08 '20 at 15:59

1

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

answered Dec 08 '20 at 15:59

Gordon Linoff

1,242,037
58
646
786

thank you so much @gordon! works like a charm. – tiredqa_18 Dec 09 '20 at 02:14

score 1 · Answer 2 · answered Dec 08 '20 at 16:13

1

You can use row_number analytical function as follows:

Select * from
(Select t.*,
       row_number() over (partition by cust_id, date_trunc('month', order_date) 
                                  order by order_date) as rn
From your_table t) t
Where rn = 1

answered Dec 08 '20 at 16:13

Popeye

35,427
4
10
31

thank you @popeye. this works as well. if i can hide the extra column created would be great too – tiredqa_18 Dec 09 '20 at 02:23
You can write the name of columns in select clause as per your requirement. – Popeye Dec 09 '20 at 02:29

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

2 Answers2