MySQL - Select last record from second table matching with first table

Question

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.

score 21 · Answer 1 · edited Feb 21 '14 at 13:52

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

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)` — FosAvance, Jan 13 '17 at 19:20

score 13 · Accepted Answer · answered May 28 '13 at 06:47

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).

answered May 28 '13 at 06:47

Meherzad

8,433
1
30
40

1

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. – Alyas May 28 '13 at 07:02
6

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) ` – Alyas May 28 '13 at 07:02
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 – Meherzad May 28 '13 at 07:03
@Alyas Very nice solution. Thank you. – Charas May 26 '17 at 03:53
this works, but what if I have a datetime field ,and I want to use that fields?@Meherzad – Dec 06 '17 at 08:31
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 – Haseeb Javed Dec 30 '22 at 09:59

score -1 · Answer 3 · edited Jan 08 '14 at 00:11

-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;

edited Jan 08 '14 at 00:11

JYelton

35,664
27
132
191

answered May 28 '13 at 07:07

Jeeva

1

This would only return one customer's last order information, while i needed each customer's last order info. – Alyas May 28 '13 at 11:16

MySQL - Select last record from second table matching with first table

3 Answers3