Custom Search
 


Get single records when duplicate records exist



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



Other Recent Articles from the Database SQL category:

1.Find duplicate values or non-duplicate values in a table
2.How to get Top 1 record from Oracle by using PL/SQL
3.How to get Top N rows from Oracle by using SQL
4.How the data types in Access Northwind are converted to Oracle
5.How to do cross table update in Oracle
6.Export Northwind Access database to MySQL via ODBC

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy