left Join with right table filtered for every rows-CodePudding

I have two tables:

Customer: customer_id
Order: order_id, customer_id, createdAt

I want to retry for every customer the only last order.

I tried to do this:

    SELECT C.customer_id, ORD.*
    FROM "Customer" as C
    LEFT JOIN (
            SELECT "Order".customer_id, "Order".order_id, "Order"."createdAt"
            FROM "Order", "Customer"
            WHERE "Order".customer_id = "Customer".customer_id
            ORDER BY "Order"."createdAt" DESC limit 1
    ) ORD ON C.customer_id = ORD.customer_id

but the result is:

Customer.customer_id	Order.order_id	createdAt
1	null	null
2	null	null
3	123	2022-03-04

The result is correct only for a customer. I lose the information of the others.

I also tried to do this:

    SELECT  C.customer_id, ORD.*
    FROM    "Customer" as C
    LEFT JOIN "Order" as ORD
    ON ORD.customer_id = (
                    SELECT  ORD.customer_id
                    FROM    "Order" as ORD
                    WHERE   ORD.customer_id = C.customer_id
                    ORDER BY
                                    ORD."createdAt" DESC limit 1
                    )

the result in this case is:

Customer.customer_id	Order.order_id	createdAt
1	119	2022-01-04
2	120	2022-01-04
3	123	2022-03-04
3	121	2022-02-04

I have more information about customer 3, when I just need the row where order_id is 123.

I've been working on it for a couple of days, could someone help me? Thanks so much.

CodePudding user response：

You want the most recent order for each customer. But the LIMIT 1 in your subquery means you only get the single most recent order (for any customer).

Let's work out a subquery to get the most recent order for each customer. Start with the date of each customer's most recent order.

            SELECT customer_id, MAX(createdAt) createdAt
              FROM ORD
             GROUP BY customer_id

Then let's do an inner join to that subquery. This should get us the detail of the latest order for each customer. Test it to convince yourself it works properly.

        SELECT ORD.*
          FROM ORD
          JOIN ( 
                SELECT customer_id, MAX(createdAt) createdAt
                  FROM ORD
                 GROUP BY customer_id
               ) latest ON ORD.customer_id = latest.customer_id
                       AND ORD.createdAt = latest.createdAt

That's the subquery to use in place of the one you have.

SELECT C.customer_id, ORD.*
FROM "Customer" as C
LEFT JOIN (
        SELECT ORD.*
          FROM ORD
          JOIN ( 
                SELECT customer_id, MAX(createdAt) createdAt
                  FROM ORD
                 GROUP BY customer_id
               ) latest ON ORD.customer_id = latest.customer_id
                       AND ORD.createdAt = latest.createdAt
    ) ORD ON C.customer_id = ORD.customer_id

This sort of thing probably works on multiple database vendors.

CodePudding user response：

Use this query.. Hope it will cover your requirement.

select
  cust.customer_id,
  max(ord.order_id) AS order_id,
  max(ord.createdAt) AS createdAt
from Customer as cust 
join "Order" ord on cust.customer_id = ord.customer_id  
group by cust.customer_id;

CodePudding user response：

Your derived table (subquery in the from clause) only selects one row. Your are using a join syntax here that got deprecated in the early 1990s. Here is your subquery with an up-to-date syntax:

select o.customer_id, o.order_id, o.createdat
from "Order" o 
join customer c on c.customer_id = o.customer_id
order by o.createdat desc 
limit 1;

Or shorter without the superfluos join:

select customer_id, order_id, createdat
from "Order"
order by createdat desc
limit 1;

In your main query you join this to your customer table and the one row that the subquery produces does get joined. It's just not what you actually intended.

One way to get the last order per customer is by using a window function such as ROW_NUMBER OVER or MAX OVER:

select c.customer_id, ord.*
from customer as c
left join
(
  select 
    o.*,
    row_number() over (partition by order_id order by createdat desc) as rn
  from "Order" o
) ord on ord.customer_id = c.customer_id and ord.rn = 1
order by c.customer_id;

Here is another approach using a fetch with ties clause:

select c.customer_id, ord.*
from customer as c
left join
(
  select *
  from "Order"
  order by row_number() over (partition by order_id order by createdat desc)
  fetch first row with ties
) ord on ord.customer_id = c.customer_id
order by c.customer_id;

And here is the same with a lateral join:

select c.customer_id, ord.*
from customer as c
left join lateral
(
  select o.*
  from "Order" o
  where o.customer_id = c.customer_id
  order by o.createdat desc
  fetch first row only
) ord on true
order by c.customer_id;