What is the most efficient way to write query to get latest item from collection?

Question

Hi,

Sorry about the title. That was the best I could come up with.

Anyways, here is my question - I have 2 tables, a Customer table and Order table as shown below:

Customer {
  Long id;
  String name;
  String address;
  Timestamp createdOn;
}

Order {
  Long id;
  String productName;
  Long customerId;
  Timestamp createdOn;
}

There is a one to many relation from Customer to Order, i.e., a Customer can have many Orders as identified by 'customerId'.

Now here is my question. I am looking to retrieve a list of all the Customers along with their latest Orders. Based on the above table this is what I have been able to come up with so far:

SELECT *
FROM CUSTOMER AS CUSTOMERS
INNER JOIN (
  SELECT *
  FROM ORDER AS BASE_ORDERS
  INNER JOIN (
    SELECT MAX_ORDERS.CUSTOMERID,
      MAX(MAX_ORDERS.CREATEDON)
    FROM ORDER AS MAX_ORDERS
    GROUP BY CUSTOMERID
  ) AS GROUPED_ORDERS
  ON BASE_ORDERS.CUSTOMERID = GROUPED_ORDERS.CUSTOMERID
    AND BASE_ORDERS.CREATEDON = GROUPED_ORDERS.CREATEDON
) AS LATEST_ORDERS
ON CUSTOMERS.ID = LATEST_ORDERS.CUSTOMERID

Is there a better way to do this? The only other way that I can think of is to add a new field called 'isLatest' and set that true each time a new order is placed for a customer.

Let me know what you think.

Answer 1

I find this variation a little cleaner:

select c.*, o.*
from (
    select customerid, max(createdOn) as MaxCreatedOn
    from Order
    group by customerid
) mo
inner join Order o on mo.customerid = o.customerid and mo.MaxCreatedOn = o.createdOn
inner join Customer c on o.customerid = c.id

Answer 2

What about something like this:

SELECT
 c.*,
 o.*
FROM
 Customers c
INNER JOIN
 Orders o ON o.customerId = c.id
GROUP BY
 c.id
ORDER BY
 o.createdOn DESC

Not sure how efficient this will be with the Group and Order By statements, but I believe that it will get you the data that you are looking for.