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
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
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!
