0

I have two tables : orders and clients. Clients have one or more Orders (JOIN condition : clients.id = orders.client_id).

I need to retrieve every client with their most "recent" order, based on a date column (named created) in orders table, in one request if possible.

Everything I tried retrieve each client with the first order found, not the last (and I can't rely on the primary key for that).

How should my request look like?

I could try a subquery with a MAX(created) but two problems:

  • There could be more than one order with the same date. I'd need to order them by ID but it's impossible inside a subquery?
  • I need to retrieve more than just the date column.
Ecorce
  • 123
  • 2
  • 11
  • What is the primary key in orders? If it's ID you say you cannot rely on it - why not and if true then there is no obvious way to get the last order when there are 2 on the same date.. please add your table definitions, sample data and expected result as tect to the question. – P.Salmon Jul 24 '20 at 14:19
  • The primary key for both table is a standard "id" with auto_increment. I can't rely on id because the a first order (id 1) could have been inserted with a date (2020-07-24), then a second order (id 2) with an older date (2020-01-01). I think in my case, stating that ordering two same dates by the most recent ID would be the thing to do, as the auto_increment ID is some kind of date in a way : 2 has been created after 1. But I need to rely on date first. – Ecorce Jul 24 '20 at 14:26

0 Answers0