15

I have two tables customers and orders, below is the structure.

Table - customers

  • id
  • customer_name

Table - orders

  • id
  • order_id
  • customer_id

customers table have customers records and orders table have orders placed by customers,

customer_id in orders table is linked to the id field of customers table.

Now one customer can have zero or one or more than one orders, i want to get the last order placed by customers only.

when i run the following query a simple invisible join, it returns all the orders by the customer

SELECT customers.customer_name,orders.order_id FROM orders,customers WHERE orders.customer_id=customers.id

I have also tried different JOIN statements but cannot get the last order by the customer, i want to get it in one SQL query for all customers.

Thank you in advance for your help.

Improve this question

3 Answers 3

Reset to default
21

In MySQL there is just few ways to make it work (that I now actually). The first one is sort your table as desc before the join:

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN orders o 
    ON o.id = (SELECT id FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 1)

Using in real time is the only way to get it done, but if you need to make some join on not real time you can create a temporary table or a alias table sorting it to make your select, like this:

CREATE TABLE tmp_your_table AS 
SELECT * FROM orders ORDER BY id DESC

So now you are able to make this join work: 

SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c 
INNER JOIN tmp_your_table o ON o.id = tmp_your_table.id
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This part helped me for my query INNER JOIN orders o ON o.id = (SELECT id FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 1)
13

Try this query 

SELECT 
   c.customer_name, 
   max(o.order_id)
FROM 
   customers c
INNER JOIN
   orders o
ON
   o.customer_id = c.id
GROUP BY 
   c.customer_name

You don't have any date field in the order table so assuming the latest order will be the one which has max(order_id).

Improve this answer

5 Comments

Thank you for your quick response, if order_id is random then this wouldn't work, but this is the nearest to the what i am looking for.
I have solved it with the following; SELECT c.customer_name, o.customer_id, o.order_id,o.id FROM customers c INNER JOIN orders o ON o.id = (SELECT id FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 1)
Yes it would won't work if order_id is random, then you should have date field from which we can get a order_date and find the latest order
this works, but what if I have a datetime field ,and I want to use that fields?@Meherzad
hello everyone. i am more than happy to write this comment as today in 2022 and this query from @Ayas has saved my day. Thanks
-1

Try this query

SELECT 
   c.customer_name, 
   o.order_id
FROM 
   customers c
INNER JOIN
   orders o
ON
   o.customer_id = c.id
ORDER BY 
   o.id desc
LIMIT 1;
Improve this answer

1 Comment

This would only return one customer's last order information, while i needed each customer's last order info.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.