Find the details of the last order placed by each customer

Aggregate functions are very useful for doing things such as finding the maximum price paid for an item, minimum price etc, first order date, last date ordered etc, but what do you do when you want to find the details about those items. This is a situation when the use of aggregates combined with sub selects is very handy.

Problem: What is the order_id, order total and order date and customer name of the last order placed by each customer

SELECT c.customer_name, o.order_total, o.order_date FROM (customers As c INNER JOIN orders As o ON c.customer_id = o.customer_id) INNER JOIN (SELECT max(order_date) As lastorderdate, customer_id FROM orders GROUP BY customer_id) As mo ON mo.lastorderdate = o.order_date AND mo.customer_id = c.customer_id

The above will work on most databases supporting sub selects in joins (derived tables) - MS Access 2000+, PostGreSQL all version, Oracle, MySQL 5 to name a few.

Problem: What is the order_id, order total and order date and customer name and sales rep of the very last set of orders placed regardless of customer?

This particular problem while it looks very similar to the above is much simpler to solve and can be solved with something like the below. The syntax is slightly different from database to database

PostGreSQL and MySQL Syntax

SELECT c.customer_name, o.order_total, o.order_date FROM (customers As c INNER JOIN orders As o ON c.customer_id = o.customer_id) ORDER BY o.order_date DESC LIMIT 1

Microsoft SQL Server, MS Access Syntax

SELECT TOP 1 c.customer_name, o.order_total, o.order_date FROM (customers As c INNER JOIN orders As o ON c.customer_id = o.customer_id) ORDER BY o.order_date DESC