In the article, you will learn an introduction to SQL Server SELECT DISTINCT clause to retrieve the only distinct values in a specified list of columns.
In SQL, The DISTINCT keyword is mainly used in conjunction with the SELECT statement to eliminate all the duplicate records and select unique values in SQL
The SELECT DISTINCT statement is used to return only unique values in SQL. Inside a table, a column often contains many duplicate records; and sometimes we only want to list the different (distinct) values.
Inside a table, a column often contains many duplicate values; and sometimes we need unique from the list of the different (distinct) values.
SELECT DISTINCT Syntax
Use DISTINCT in Single Column
SELECT DISTINCT <TableColumn1> FROM <YourTableName>;The given above query returns only distinct values in the specified column which means it removes the duplicate values in the column from the result set.
Use DISTINCT in Single Columns
SELECT DISTINCT <TableColumn1>, <TableColumn2>,... FROM <YourTableName>;
The query uses the combination of values in all specified columns in the SELECT list to evaluate the uniqueness.
If we apply the DISTINCT clause to a NULL column, the DISTINCT clause will keep only one unique NULL and eliminate the other. That means, the DISTINCT clause treats all NULL “values” as the same value.
SQL Server SELECT DISTINCT Examples
Consider the EmployeeInfo table having the following records as given below
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |
| Saurab | Rai | Pokhara | 10 |
| Sylvia | Gurung | Butwal | 21 |
First, let's see how the following SELECT query returns the duplicate salary records.
SELECT FirstName FROM EmployeeInfo ORDER BY FirstName;This would produce the following result, where the FirstName (Sylvia) is coming twice which is a duplicate record from the original table.
| FirstName |
| Rahul |
| Saurab |
| Sylvia |
| Sylvia |
| Ubina |
Now, let us use the DISTINCT keyword with the above SELECT query and then see the result.
SELECT DISTINCT FirstName FROM EmployeeInfo ORDER BY SALARY;This would produce the following result where we do not have any duplicate values.
| FirstName |
| Rahul |
| Saurab |
| Sylvia |
| Ubina |
Difference between select and select distinct in SQL
The main difference between the SELECT and SELECT DISTINCT statements in SQL is that SELECT returns all rows that match the specified criteria, while SELECT DISTINCT only returns distinct (unique) rows. In other words, SELECT DISTINCT eliminates duplicate rows from the result set.
Select distinct on one column, with multiple columns, returned SQL
In SQL, you can use the SELECT DISTINCT ON (column_name) statement to return unique values of one column while still returning multiple columns. The syntax would be:
SELECT DISTINCT ON (column_name) column1, column2, column3, ...
FROM table_name
ORDER BY column_name, other_column;
The DISTINCT ON clause is followed by the column name(s) on which you want to retrieve the unique values. The SELECT statement then lists the columns that you want to return. The ORDER BY clause is used to specify the sort order of the rows and must include the column(s) specified in DISTINCT ON, followed by any other columns you want to include for sorting.
This will return the first row for each unique value in column_name, along with the other columns specified in the SELECT statement.
For example, if you want to retrieve unique values of column 'id' and columns 'name', 'age', and 'gender' from table 'employees', you could use the following query:
SELECT DISTINCT ON (id) id, name, age, gender
FROM employees
ORDER BY id, age;
This will return all unique values of column 'id' with its corresponding 'name', 'age', and 'gender' values while sorting it by 'id' and 'age'
SQL SELECT DISTINCT on one COLUMN
To retrieve distinct (unique) values of one column in SQL, you can use the SELECT DISTINCT statement. The syntax for this is:
SELECT DISTINCT column_name
FROM table_name;
For example, if you want to retrieve all unique values of the "last_name" column from the "employees" table, you would use the following query:
SELECT DISTINCT last_name
FROM employees;
This will return a list of all the unique last names of employees in the table, without any duplicates.
You can also use the ORDER BY clause to sort the result set in a specific order.
SELECT DISTINCT last_name
FROM employees
ORDER BY last_name;
This will return the unique last names of employees in alphabetical order.
SELECT DISTINCT Multiple COLUMN
To retrieve distinct (unique) values of multiple columns in SQL, you can use the SELECT DISTINCT statement followed by a list of the column names. The syntax for this is:
SELECT DISTINCT column1, column2, column3, ...
FROM table_name;
For example, if you want to retrieve all unique combinations of the "first_name" and "last_name" columns from the "employees" table, you would use the following query:
SELECT DISTINCT first_name, last_name
FROM employees;
This will return a list of all the unique combinations of first and last names of employees in the table, without any duplicates.
You can also use the ORDER BY clause to sort the result set in a specific order, for example:
SELECT DISTINCT first_name, last_name
FROM employees
ORDER BY last_name, first_name;
This will return the unique combinations of first and last names of employees in alphabetical order of last_name and then first_name.
Keep in mind that the order of columns in the SELECT DISTINCT statement will determine what is considered a unique combination.
SELELCT DISTINCT COUNT
To retrieve the count of distinct (unique) values in a column in SQL, you can use the COUNT() function in combination with the SELECT DISTINCT statement. The syntax for this is:
SELECT COUNT(DISTINCT column_name)
FROM table_name;
For example, if you want to retrieve the number of unique last names in the "employees" table, you would use the following query:
SELECT COUNT(DISTINCT last_name)
FROM employees;
This will return the number of unique last names in the employees table.
You can also use this approach to count the distinct values of multiple columns. For example, to count the number of unique combinations of first and last names in the employees table, you can use the query:
SELECT COUNT(DISTINCT first_name, last_name)
FROM employees;
This will return the number of unique combinations of first and last names in the employees table.
It's also possible to use this approach with a condition, for example:
SELECT COUNT(DISTINCT last_name)
FROM employees
WHERE salary > 50000;
This will return the number of unique last names among the employees that have a salary greater than 50000.