Question: I have two tables:

| customers |  |  |
| --- | --- | --- |
| id | name | order\_id |

| orders |  |  |  |
| --- | --- | --- | --- |
| id | customer\_id | date | amount |

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

```sql
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:

```sql
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
> 
> 
>

Answer: 
Use [distinct on](https://stackoverflow.com/questions/46566602/what-does-distinct-on-expression-do) w/o `group by`. You may see [this](https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by) SO discussion for details.

```sql
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;

```
```
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