|
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.
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.
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 © 2024 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 |