SQL Select Distinct
The SQL SELECT DISTINCT statement is used to retrieve unique values from a specified column or set of columns in a database table. It ensures that the result set contains only distinct (unique) values, eliminating any duplicate entries. Here’s the basic syntax for the SELECT DISTINCT statement:
|
1 2 |
SELECT DISTINCT column1, column2, ... FROM table_name; |
SELECT DISTINCT: This part of the statement specifies that you want to retrieve distinct values.column1, column2, ...: These are the columns from which you want to retrieve distinct values. You can specify one or more columns.FROM table_name: This part of the statement specifies the table from which you want to retrieve the data.
Here’s an example to illustrate how to use the SELECT DISTINCT statement:
Suppose you have a table called “Employees” with the following data:
| EmployeeID | FirstName | LastName |
|---|---|---|
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | John | Doe |
| 4 | Mary | Johnson |
If you want to retrieve a list of distinct first names from the “Employees” table, you can use the following SQL query:
|
1 2 |
SELECT DISTINCT FirstName FROM Employees; |
The result of this query would be:
| FirstName |
|---|
| John |
| Jane |
| Mary |
As you can see, the SELECT DISTINCT statement eliminated the duplicate “John” entry from the result set, returning only unique first names.
You can use SELECT DISTINCT with one or more columns, depending on your requirements, to retrieve distinct combinations of values from multiple columns.
In SQL, you can use the SELECT DISTINCT statement in combination with the COUNT function to count the number of distinct values in a specific column or set of columns. This is useful when you want to find the count of unique values in a column. Here’s the syntax for counting distinct values:
|
1 2 |
SELECT COUNT(DISTINCT column1) FROM table_name; |
SELECT COUNT(DISTINCT column1): This part of the statement selects and counts the distinct values in “column1.”FROM table_name: This part of the statement specifies the table from which you want to retrieve and count the distinct values.
Here’s an example to demonstrate how to use SELECT DISTINCT with COUNT:
Suppose you have a table called “Orders” with the following data:
| OrderID | CustomerName |
|---|---|
| 1 | John |
| 2 | Jane |
| 3 | John |
| 4 | Mary |
To count the number of distinct customer names in the “Orders” table, you can use the following SQL query:
|
1 2 |
SELECT COUNT(DISTINCT CustomerName) FROM Orders; |
The result of this query would be:
| COUNT(DISTINCT CustomerName) |
|---|
| 3 |
In this example, the COUNT(DISTINCT CustomerName) expression counts the distinct customer names in the “CustomerName” column, and the result is 3, indicating that there are three unique customer names in the “Orders” table.
You can also count distinct values in multiple columns by including more columns in the SELECT clause, such as COUNT(DISTINCT column1, column2), if needed.
