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. In the example above, Order_ID is the primary key column in Orders table so Order_IDs are 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 (single column) or composite unique index (multiple columns) defined.

Below is an example shows how non-unique columns are used that produce duplicate records in the result.

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.

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 achieved
    -- 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