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;

```
Then don't use min at the main clause.

`min(orders.date)` means that you want to get the minimum date for one column.
You can filter order first before joining

```
SELECT 
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id 
INNER JOIN (
        SELECT customer_id, Min(date) as MinDate 
        FROM orders 
        GROUP BY customer_id
    ) filtered_order 
        ON filtered_order.customer_id = customers.id 
        AND filtered_order.MinDate = orders.date

```