|Back to Papers and Articles|
SQL Cheat Sheet: Query By Example - Part 2
Copyright 2006 Paragon Corporation ( June 03, 2006)
Find the details of the last order placed by each customerAggregate 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
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
Microsoft SQL Server, MS Access Syntax
Back to Papers and Articles