SQL SELECT UNIQUE : Searching for Distinct Values

Introduction to SELECT DISTINCT

select distinctThere 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_IDSTUDENT_CLASS
04521Oracle 101
04522Intro to SQL
04523Intro to SQL
04524Database Administration
04525Report Generation
04526Database Administration
04527Database Administration
04528Database Administration
04529Oracle 101
04530Database Administration
04531Intro to SQL
04532Database Administration
04533Database 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_IDCUST_CITYCUST_STATE
101FloydNew York
202RaleighNorth Carolina
303FloydVirginia
404LouisvilleKentucky
505WilmingtonNorth Carolina
606WilmingtonNorth 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_CITYCUST_STATE
FloydNew York
RaleighNorth Carolina
FloydVirginia
LouisvilleKentucky
WilmingtonNorth 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_CITYCUST_STATE
FloydNew York
FloydVirginia
LouisvilleKentucky
RaleighNorth Carolina
WilmingtonNorth 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_NUMCONTESTANT_WORD
01undoubtedly
02quizzes
03disappearance
04explanation
05disappearance
06maneuver
07pronunciation
08perseverance
09explaination
10precedence

 

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.