SQL SELECT UNIQUE : Searching for Distinct Values

Introduction to SELECT DISTINCT

There may be cases in which you want to retrieve data from fields while also eliminating duplicate results from the output of your queries. The SELECT DISTINCT statement can be used along with conditions, such as specific columns, to retrieve unique field data from tables. There are many different reasons that one may have for requesting unique values from fields. The SELECT DISTINCT statement can be very useful with tasks such as those involved in analysis and reporting.

Beginning to explore SQL queries?

The essential syntax of a SELECT DISTINCT statement is as follows:

SELECT DISTINCT <condition, such as column>

FROM <table>;

Difference between SELECT UNIQUE and SELECT DISTINCT

SELECT UNIQUE: one might say that there is something “distinctly” different about that statement (pun intended). Although SELECT UNIQUE and SELECT DISTINCT are synonymous since the queries essentially perform the same function, DISTINCT has become a standard SQL keyword. UNIQUE and DISTINCT are reserved Oracle keywords. The recommendation is to use the DISTINCT keyword since it has more support than the UNIQUE keyword.

Want to focus on an extension of Standard SQL, such as Oracle SQL?

Using SELECT DISTINCT

Time to look at the helpful DISTINCT clause in action with some demonstrations of associated concepts.

The first example:

In the first example, you have a table being used for tracking classes the students have enrolled in this semester. The school would like to have a list of unique classes that students have enrolled in because they would like to see which classes that have available that are being utilized this semester.

Student class enrollment table contents: student ID number and class name

Table name: enrollments

Columns:  STUDENT_ID, STUDENT_CLASS

 

SELECT *FROM enrollments;

STUDENT_ID	STUDENT_CLASS
04521	Oracle 101
04522	Intro to SQL
04523	Intro to SQL
04524	Database Administration
04525	Report Generation
04526	Database Administration
04527	Database Administration
04528	Database Administration
04529	Oracle 101
04530	Database Administration
04531	Intro to SQL
04532	Database Administration
04533	Database Administration

 

You can see that it appears that “Database Administration” is a popular class this semester; however, the school does not want to see which class is the most popular at this time. The school requests to see which classes are being taken this semester and we will use SELECT DISTINCT to fulfill their request. SELECT DISTINCT will be used along with the STUDENT_CLASS column and the enrollments table.

SELECT DISTINCT STUDENT_CLASS

FROM

enrollments;

 

STUDENT_CLASS

Oracle 101

Intro to SQL

Database Administration

Report Generation

 

Now you can see a list of the different classes being taken this semester.

Our second example:

The second example is a table used for a magazine publication. The publication company would like to see a list of the different cites that their subscribers live in, a SELECT DISTINCT statement could be used along with the table used for subscriber profiles and the city column to retrieve the unique cities associated with subscriber profiles. In the magazine publication’s case, if a SELECT statement is used without the DISTINCT clause, then they would have output that included duplicate cases of cities which would not be as efficient with determining which cities their subscribers live in and the output would be relatively convoluted.

Subscriber location profile table contents: customer ID number, customer city, and customer state

Table name: locations

Columns:  CUST_ID, CUST_CITY, and CUST_STATE

SELECT * FROM locations;

 

CUST_ID	CUST_CITY	CUST_STATE
101	Floyd	New York
202	Raleigh	North Carolina
303	Floyd	Virginia
404	Louisville	Kentucky
505	Wilmington	North Carolina
606	Wilmington	North Carolina

 

As you can see, there is more than one entry for the city of Floyd and more than one entry for the city of Wilmington

You could use the following to select the unique values from the CUST_CITY column:

SELECT DISTINCT CUST_CITY

FROM locations;

 

CUST_CITY

Floyd

Raleigh

Louisville

Wilmington

 

Duplicate values have been eliminated from the output. “Wait…….there had been entries for different states that both have cities named Floyd in the locations table”, one could express. In this case, our query should become a bit more refined.

For cases in which more than one column should be included when finding unique values, the DISTINCT keyword can be used along with a table and a combination of columns. In our example, it would be appropriate to combine the CUST_CITY and CUST_STATE columns.

SELECT DISTINCT CUST_CITY, CUST_STATE

FROM locations;

 

CUST_CITY	CUST_STATE
Floyd	New York
Raleigh	North Carolina
Floyd	Virginia
Louisville	Kentucky
Wilmington	North Carolina

Now, you see that unique locations are displayed because all of the city-state combinations are unique.

You can then display the output in an orderly fashion by using the ORDER BY clause. Note that if a combination of columns is used in the SELECT DISTINCT query, then the same combinations of columns should also be used with the ORDER BY clause.

 

SELECT DISTINCT CUST_CITY, CUST_STATE

FROM locations

ORDER BY CUST_CITY, CUST_STATE;

 

CUST_CITY	CUST_STATE
Floyd	New York
Floyd	Virginia
Louisville	Kentucky
Raleigh	North Carolina
Wilmington	North Carolina

 

 

The third example:

In the third example, you have a table for an organization that had held a spelling contest. The organization requests to see which words had been misspelled.

Contest table contents: contestant number and misspelled words.

Table name: misspelled

Columns:  CONTESTANT_NUM, and CONTESTANT_WORD

SELECT * FROM misspelled;

 

CONTESTANT_NUM	CONTESTANT_WORD
01	undoubtedly
02	quizzes
03	disappearance
04	explanation
05	disappearance
06	maneuver
07	pronunciation
08	perseverance
09	explaination
10	precedence

 

As you can see, more than one instance of some words being misspelled had occurred with the contestants.

SELECT DISTINCT CONTESTANT_WORD

FROM

misspelled;

 

CONTESTANT_WORD

undoubtedly

quizzes

disappearance

explanation

maneuver

pronunciation

perseverance

explaination

precedence

 

In this case, you find that the query not only helps you locate unique values, but that your query also helps you discover a word that had been input incorrectly into the database. We can see that “explanation” is displayed as well as “explaination”. You could have the information corrected and could then perform the query, which would display the following output:

 

CONTESTANT_WORD

undoubtedly

quizzes

disappearance

explanation

maneuver

pronunciation

perseverance

precedence

 

We could then use ORDER BY to have our results displayed in an ordered format:

 

SELECT DISTINCT CONTESTANT_WORD

FROM

misspelled

ORDER BY CONTESTANT_WORD;

 

CONTESTANT_WORD

disappearance

explanation

maneuver

perseverance

precedence

pronunciation

quizzes

undoubtedly

 

Conclusion 

SELECT DISTINCT retrieves unique values from fields which can help with an array of tasks that can vary from reporting to locating errors in database input. Ready to explore SQL reporting services?

Not only does SELECT DISTINCT help with retrieving the data being requested, it can also help with ensuring that output is displayed in a way that is easier to read.