CODEWITHSUNDEEP
sqlpostgresqlgroup-bydistinct-on
tiredqa_18
tiredqa_18

Reputation: 331

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

Upvotes: 4

Views: 1934

Answers (2)

Popeye
Popeye

Reputation: 35920

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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

Upvotes: 3

Related Questions