I have two tables:

I would like to get the first order for each customer so I used inner join & min on date. however, I'd like to get all order columns as well.

My SQL query is

SELECT 
    customers.id, customers.name, 
    MIN(orders.date) 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY   
    customers.id;

When I try to add more columns from orders table as following:

SELECT 
    customers.id, customers.name, 
    MIN(orders.date), orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY 
    customers.id;

I get an error

ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

CodePudding user response：

Use distinct on w/o group by. You may see this SO discussion for details.

SELECT distinct on (customers.id)
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
ORDER BY BY 
    customers.id, orders.date;

CodePudding user response：

select oo.*,c.*,fo.* from 
(select o.customer_id,min(o.date)order_date 
from orders o
group by o.customer_id)fo
inner join orders oo on oo.customer_id=fo.customer_id and oo.date=fo.order_date
inner join customer c on c.id=oo.customer_id

I think this will help you. In SQL Group by clause, you can only select grouped column and aggregated columns. For your case, you have to add extra join to take whole data from order table using customer_id and minimun order_date

CodePudding user response：

you can write this query

SELECT customers.id, customers.name, min(orders.date) 
FROM customers
FULL JOIN orders ON customers.id=orders.customer_id
GROUP BY customers.id;