postgresql group by

Question

PostgreSQL

Good afternoon!
There is a table order: id | customer_id | created | category_id
There is a query select customer_id, date_trunc('day', min("order".created)) as created from "order" group by 1
It correctly selects the data that is needed, in fact, the user ID with the minimum date of his order.
It is necessary to pull out another category_id
That is,
select customer_id, date_trunc('day', min("order".created)) as created, category_id from "order" group by 1,3
such a query gives side rows, you need to select the category_id of that order, from which min("order".created)
Can you tell me how?

Answer 1

Through DISTINCT should work:

select distinct on (customer_id) customer_id, date_trunc('day', «order».created) as created, category_id from «order» order by 1, «order».created DESC

Answer 2

group by in this case reduces how it lies in the database, as I understand it, you probably need to look in the direction of having. www.postgresql.org/docs/8.1/static/tutorial-agg.html

Answer 3

select the category_id of the order that has min("order".created)

select customer_id, created, category_id 
from 
  (select customer_id, date_trunc('day', «order».created) as created, category_id 
          ,row_number() over (partition by customer_id order by date_trunc('day', «order».created)) rn
   from «order»)s
where rn = 1

Answer 4

select distinct on(customer_id) customer_id,category_id,date_trunc('day', min(created)) as createdon from testtable group by customer_id,category_id order by customer_id,createdon