If you have been doing SQL development for a while, you probably have come across this common scenario in your everyday job - Retrieving a single record from a table when there are multiple records exist for the same entity such as customer. For example, a customer can place many orders and you need to find out the latest order each customer placed.

In this scenario, we'll get a list of latest order IDs for all customers by using the max function on Order_ID column. Because each Order_ID is unique, we can then select all the orders where their Order_IDs are in this Order_ID list.

The common technique to do this kind of task is this - use the max or min function on a column that contains unique values to obtain a single record for each entity that we are interested in, such as customers (to get a single order for each customer).

Queries on this page use the Oracle Northwind database that was converted from the popular Access Northwind database.

Below is a screenshot from Oracle Northwind Orders table showing last order highlighted in yellow for each customer.

In the query below, the sub-query retrieves each customer's maximum Order_ID in order to uniquely identify each order.

-- Get the last order each customer placed.
select a.Order_ID, 
    b.Customer_CODE, 
    b.Company_Name, 
    a.Order_Date 
from Orders a
inner join Customers b on a.Customer_ID=b.Customer_ID
where a.Order_ID in 
-- Below is the sub-query
(
    -- Get the max order id for each customer - this is achieved 
    -- by using GROUP BY on Customer_ID field and max function 
    -- on Order_ID in this sub-query.
    select max(Order_ID) from Orders 
    group by Customer_ID
)
order by b.Customer_CODE

89 records returned in the query result.

The sub-query must return unique records

Note that the data in the column on which we apply max or min function must be unique. Otherwise, multiple records may exist for each customer. The best candidate for this unique column is the primary key column or a column with unique index defined.

If two columns together can uniquely identify each entity, it's a good candidate too, but in the query below, the two columns Customer_ID and max(Order_Date) combined does not uniquely identify each order so it poses a problem in the result.

The query shown below is supposed to use Customer_ID and max(Order_Date) to get the latest order for a customer. It gets the orders that a customer placed on the latest order date. Because a customer may place multiple orders on a single date, this query could potentially retrieve more than one order for a customer.

select a.Order_ID, 
    b.Customer_CODE, 
    b.Company_Name, 
    a.Order_Date 
from Orders a
inner join Customers b on a.Customer_ID=b.Customer_ID
where (a.Customer_ID, a.Order_Date) in 
(
    -- Get the max order date for each customer - this is acheived 
    -- by using GROUP BY in this sub-query.
    select Customer_ID, max(Order_Date) from Orders 
    group by Customer_ID
)
order by b.Customer_CODE

As we can see from the result below, customer LACOR (La corne d'abondance) placed two orders on 24/03/1998. This is not what we wanted in the first place. As a result, 90 records are returned by the query. This problem does not occur in the first query where Order_ID is used to uniquely identify each order.

Happy Ordering!


Copyright© GeeksEngine.com