Services

Technologies

Industries

About Us

Our Work - Case Studies

The Select Distinct Statement in SQL

Select Distinct Statement, Introduction, Syntax and Use Cases

One of the most useful functions in SQL is the SELECT DISTINCT statement

*The SQL function which inspired our company name

Introduction to the SELECT DISTINCT statement in SQL

The SELECT DISTINCT statement is used to return only unique values from a table. Inside a table, a column often contains many duplicate values, and sometimes you only want to list the different (distinct) values.

The SELECT DISTINCT function is important to use because it eliminates duplicative rows from the result set of a query. The resulting output consists of unique values only, effectively streamlining the data for further analysis or manipulation. This can be especially useful when working with large datasets or when you only need to see the unique values in a column.

Syntax of the SELECT DISTINCT statement

The basic syntax of the SELECT DISTINCT statement is:

SELECT DISTINCT column1, column2, …
FROM table_name;

Here, column1, column2, … are the fields that you want to retrieve unique values for. table_name is the name of the table that contains the fields.


An Example of the SELECT DISTINCT statement in action

We have a data set containing sales transactions by customer

But we need to extract a unique list of Customer IDs

Sales transactions by customer

select distinct [CustomerID]

from [AdventureWorks2019].[dbo].[vw_Sales_by_Customer]

order by [CustomerID]

The select distinct command returns a list from the data source with all duplicates removed

Distinct list of customers generated using select distinct statement in SQL

Use Cases for the SELECT DISTINCT statement

Building a data warehouse from flat data files such as sales transaction is a good example of a use case and one which we use extensively, By extracting the unique Product IDs and Product details such as names from a large data file allows us to build a Product Dimension table to store these values only once as a single point of reference, we can then exclude these from a Sales Fact Table

Other primary use cases for this are

  • Streamlining data for further analysis or manipulation or more efficient data storage and retrieval
  • Analysing and improving data accuracy based on distinct values
  • Eliminating duplicative rows from the result set of a query

Summary of the SELECT DISTINCT statement

The SELECT DISTINCT statement is used for cases where you only want to return values that are unique, distinct, or different from one another. It is especially useful when working with large datasets or when you only need to see the unique values in a column, Its easy to learn and can be a very powerful technique

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert