How do I get a single line per customer including the first line of an order in SQL

3
votes

I have two tables - Customers, and Orders. Each Customer will have 1..n Orders. They have a relation on a Foreign Key to the Customers table in the Orders table. So far, so DB 101.

I need a query that will return a single row per Customer, together with the date and Order ID of that customer's latest order. ALL Customers have at least one order. I can easily do this with a function, but I'd prefer to do it in SQL.

2
sql-server
Look into the JOIN manual - Tikkes
I would however - in your case - create a third table. customer_orders. In this table you would link the customer_id with the different order_ids. Each record presents a link to an order in the orders table. This makes it much easier to do such things . A good explanation can be found here This technique is called normalization - Tikkes
The proposed customer_orders table would be required if an order could belong to more than 1 customer. Since there's a 1..n relation between Customer and Orders, the FKCustomerID key in the Orders table suffices. - Edwardo

2 Answers

3
votes

In case of auto incremented ID-s, and no variance with dates, the solution is that simple:

SELECT c.*, o.*
FROM Customer c 
JOIN (SELECT max(id) as order_id, customer_id 
      FROM Order GROUP BY curtomer_id) conn on c.id = conn.customer_id
JOIN Order o on o.id = conn.order_id
1
votes

You didn't state your DBMS, so I'm assuming an ANSI compliant engine.

select *
from (
    SELECT c.*, 
           o.*, 
           row_number() over (partition by o.customer_id order by order_date desc) as rn
    FROM Customer c 
      JOIN Order o on o.customer_id = c.id
)
where rn = 1