Before the introduction of the SELECT DISTINCT statement in Oracle, retrieving unique data from tables was not always straightforward. But you can easily extract unique data from tables using the SELECT DISTINCT query in conjunction with conditions specifying the type of data you are retrieving.
The DISTINCT keyword also compares every value of the column(s) included in the result set produced by the query with one another.
Consequently, if you are having difficulty using the Oracle SELECT DISTINCT statement, here is a tip on how you can use it.
How to Use the Oracle SQL SELECT DISTINCT to Query Distinct Data
Using SELECT DISTINCT will get rid of duplicate results in a single column. Consider a sample data table that contains customer data:
Table name = customer
| First_name | Last_name | Sex |
| Rick | Daniels | M |
| Khan | Joe | M |
| Rick | Philip | M |
| Vino | Praha | F |
| Khan | Joe | F |
Now, let's say we want to see a list of customer names because we want to get name tags printed or something.
So let's try writing a query without the distinct statement first.
SELECT first_name FROM customer
Output
Rick Khan Rick Vino Khan
There are some duplications. Rick's and Khan's name appears twice. How would we go about eliminating the duplicates? Each name should only be displayed once.
Let's now add the distinct statement to eliminate the duplicates:
SELECT DISTINCT first_name FROM customer
Output
Rick Khan Vino
You can also get the first_name and last_name and this will display Rick's record as it has a different last name but will display only one record for Khan, because it has the duplicate first and last name. Below is an example:
SELECT DISTINCT first_name, last_name FROM customer
Output
Rick Daniels Khan Joe Rick Phillip Vino Praha
Note: There are some duplications in the first column, though. Rick's name appears twice. But that does not matter as the difference in the second column makes this output unique.
Conclusion
You can use SELECT DISTINCT to search for unique data in tables and remove duplicate rows in the result set.
