Saturday, November 17

SQL- Distinct Operator

Distinct operator in SQL

Distinct suppresses duplicate values for the column(s) in the columns list. Distinct followed by a star (*) suppresses duplicate records.


select distinct column_name from table_name 
select distinct (*) from table_name 
Example: Consider a table employee which has a column salary with duplicate salary values, To display distinct salary values the query can be written as
select distinct(salary)from employee; //Displays distinct                                                   salary values from column salary 

Distinct on Multiple Columns
The Distinct operator is applied to two columns. Here database used is AdventureWorksDW2008


select distinct englishoccupation, englisheducation from DimCustomer

As you examine the records you’ll notice Manual more than once. You will also see Bachelors more than once. However, you won’t see Manual together with Bachelors more than once. The Distinct operator is working on both columns together.

Count(distinct), Distinct with Count

If you want to count the records excluding duplicates, you can use distinct along with count.


select COUNT(distinct (column_name)) from table_name; 
Example: Consider a table Employee which has a column Salary with duplicate salary values, To count distinct salary the query can be written as
select COUNT(distinct (salary)) from employee;