Geek asks and answers

How to get a separate line for each customer, including the first order line in SQL

I have two tables - clients and orders. Each client will have an order 1..n. They are related to the Foreign Key table to the Customers table in the Orders table. So far, DB 101.

I need a request that will return one row for the Client along with the date and order identifier of the last customer’s last order. ALL customers have at least one order. I can easily do this with a function, but I would rather do it in SQL.

+3
source share
2 answers

In the case of auto-incrementing IDs and the absence of deviations from dates, the solution is 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
+3
source

You did not specify your DBMS, so I assume that it is compatible with ANSI.

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
+1
source

More articles:


All Articles