SQL SELECT DISTINCT Clause

The SQL SELECT DISTINCT keyword is used to fetch only unique (without duplicates) values or records.

It removes all the duplicate records from query results while fetching data from table.

It can filter only unique data for a single column value or an entire records from query result.

The SQL Distinct keyword will not ignore any NULL values from query result.

Return the distinct values of the department column.

Example 1: MySQL group Concat distinct by Id :

select group_concat(Name)
from
(
   select distinct Id,Name from DemoTable
)
tbl;

Output:

Example 2: To arrange department names in ascending order, use a custom separator like '|' and an ORDER BY clause:

SELECT 
GROUP_CONCAT(DISTINCT department separator ' | ') as departments 
FROM student;

Output:

So, in the above query,

Department names are sorted in ascending order.

No repeating values are returned.

The separator is changed from ‘,’ to ‘ | ‘.

Example 3: SQL concatenate WITH SELECT and DISTINCT

SELECT CONCAT( 'Date last updated ', (select distinct max(Record_date) )
FROM [table_1]

Output:

The most latest date is taken from the table_1 field Record date, and the distinct value is returned.

SQL Server allows you to store mixed case data in your databases, but based on how your databases are built, SQL Server may ignore the case when issuing TSQL instructions.

One issue you can have is that you wish to retrieve a distinct list of values from a table to display the differences in your table, but your database is set up as case insensitive, so the DISTINCT clause doesn't reveal the differences; instead, everything gets combined around.

Example 1: Case-insensitive distinct, you need to use UPPER() or LOWER().

Case 1: Using UPPER()

The syntax is as follows:

SELECT DISTINCT UPPER(yourColumnName) FROM yourTableName;

Case 2: Using LOWER()

The syntax is as follows:

SELECT DISTINCT LOWER(yourColumnName) FROM yourTableName;

Example 2: MySQL DISTINCT case sensitive

case sensitive grouping because by default MySQL use case insensitive.

We can use binary operator to convert the character set.

SELECT DISTINCT CAST(expr as BINARY)

Example 3: The collation will be set to case insensitive.

Select distinct col1 COLLATE sql_latin1_general_cp1_cs_as From dbo.myTable

Example 4: To make the collation case sensitive on the FirstName column, we may update the query as follows.

SELECT DISTINCT TOP 10 FirstName COLLATE sql_latin1_general_cp1_cs_as
FROM Test.dbo.contact
WHERE FirstName LIKE 'A%'
ORDER BY 1;

Example 1: We have this sales table recording the name of item sold and name of the customer who bought it.

Because a customer may buy an item numerous times, counting the number of distinct items purchased by each customer is difficult:

SELECT
 COUNT(DISTINCT CASE WHEN customer_name = 'Alex' THEN item END) AS by_Alex,
 COUNT(DISTINCT CASE WHEN customer_name = 'Bob' THEN item END) AS by_Bob,
 COUNT(DISTINCT CASE WHEN customer_name = 'Jim' THEN item END) AS by_Jim
FROM sales;

Output:

Example 2: include DISTINCT in your CASE WHEN statements with COUNT. Take a look:

SELECT
 COUNT(DISTINCT CASE
 WHEN pay_date BETWEEN '2015-06-01' AND '2015-06-05'
 THEN candidate_id
 END) AS accepted_student,

 COUNT(DISTINCT CASE
 WHEN pay_date = '2015-06-06'
 THEN candidate_id
 END) AS conditionally_accepted_student,

 COUNT(DISTINCT CASE
 WHEN pay_date > '2015-06-06'
 THEN candidate_id
 END) AS not_accepted
FROM application;

The number of approved, conditionally accepted, and not accepted payments was counted.Now we're counting how many candidates were accepted for at least one degree course; how many were conditionally admitted for at least one degree course, and how many were not accepted for at least one degree course.

We had to include the keyword DISTINCT since a single candidate can register for several degree courses; thus, if a candidate paid on time for two courses, that candidate would be included twice (the candidate could still be counted twice in two distinct columns if the candidate paid for one degree course but did not pay for another, but that's another matter).

Example 3: Trying to do a case on a distinct value and it isn't going well:

SELECT CASE WHEN DISTINCT(Response_Master_Incident.Battalion) = '' THEN 'Unknown'
ELSE DISTINCT(Response_Master_Incident.Battalion) END AS 'Zone'

You can use the CASE statement to choose distinct combinations from two columns.

Example 1:

Step 1: Let us create a table with some columns.

create table select_DistinctTwoColumns
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> FirstValue char(1),
   -> SecondValue char(1),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.57 sec)

Step 2: Insert records

insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('s','t');
Query OK, 1 row affected (0.12 sec)

mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('t','u');
Query OK, 1 row affected (0.24 sec)

mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('u','v');
Query OK, 1 row affected (0.12 sec)

mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('u','t');
Query OK, 1 row affected (0.16 sec)

Step 3: Display all records from the table using select statement.

select *from select_DistinctTwoColumns;

Output:

Example 2:

SELECT DISTINCT source,destination FROM Hyperlink;

however it only gives me the possibilities. I require the one-of-a-kind combos.

Because MySQL doesn't have the least() and greatest() operators, you'll have to use a CASE construct to find the smaller/greater one. This is fine with two columns, but when more columns are added, the solution becomes a jumble:

select distinct 
 case 
  when source < destination then source 
 else destination 
 end as source,
 case 
  when source > destination then source 
 else destination 
  end as destination
from hyperlinks;

Example 3: Simply use the DISTINCT keyword:

SELECT DISTINCT Latitude, Longitude 
FROM Coordinates;

This will return values where the (Latitude, Longitude) combination is unique.

Example 4: Gets trickier because now we have to query two columns: course_id and exercise_id. Let’s try to use the same approach as before:

SELECT DISTINCT course_id, exercise_id FROM bugs;

Output:

If one needs to count the number of rows in a table while only counting identical records, SQL uses a mix of COUNT and DISTINCT.

SELECT statement in SQL You can use the SQL SELECT COUNT DISTINCT query to get the number of distinct product values added into the example database table.

Syntax:

The DISTINCT keyword used with the COUNT function.

SELECT COUNT(DISTINCT column_name) FROM table_name;

The DISTINCT keyword will only count non-null unique values. Any duplicate values will be ignored by the computers.

This statement would count all of the attribute column's distinct entries in the table. Repeated elements are only counted once when using DISTINCT.

Example 1: This SQL command counts all distinct department names from the employee’s table.

SELECT COUNT(DISTINCT dept) from employees

The SQL command returns 3 since there is 3 unique dept. There are two employees who are Intern, therefore the DISTINCT clause only counts them as one. The other two dept: Teacher and MBA are unique, so they are each counted once as well - giving a total of 3.

Example 2: Using COUNT and DISTINCT

This example returns the number of different titles that an Adventure Works Cycles employee can hold.

SELECT COUNT(DISTINCT Title)  
FROM HumanResources.Employee;  
GO  

Output:

Example 3: Correct SQL Count Distinct SELECT example:

SELECT COUNT(DISTINCT ProductId) FROM SalesOrders

Despite the fact that we added seven rows to the sample sql table, we only used four different products. The distinct count Select statement for the last SQL Server Count Distinct query is 4.

To count the distinct rows, use the count() method with distinct on several columns in a select statement.

Example 1: count the number of rows, but distinct by three parameters:

select count(distinct id,name,address) from mytable

Output:

Example 2: I need to count how many distinct items there are in this table, but the distinct is spread across two columns.

SELECT COUNT(*) 
FROM (SELECT DISTINCT DocumentId, DocumentSessionId
FROM DocumentOutputItems) AS internalQuery

My query works well, but I'm curious if there's a way to acquire the full result with just one query (without using a sub-query).

Example 3: Here is an example:

SELECT COUNT(*) 
FROM (
SELECT  DISTINCT agent_code, ord_amount,cust_code
FROM orders 
WHERE agent_code='A002');

Output:

To choose separate dates from a table's datetime column, use the DISTINCT keyword. The data type of the column is datetime, and each entry comprises dates and times. To create a dropdown list with the individual dates from the column.

Syntax:

DATETIME(datefield [, format [, interval]])

The format string is applied to the specified date field. yyyy-MM-dd HH:mm is the default pattern.

Within the format string, the following letters are permitted:

• y: year
• M: month
• d: day of month
• H: hour (0-23)
• h: hour (1-12)
• m: minute
• s: second
• E: day of week (Mon-Sun)

Example 1: query to get distinct dates:

select distinct year(UserPost),month(UserPost) from PostMesssageDemo;

Output:

Example 2: The year|month|week intervals are for a single interval. For d (days), h (hours), m (minutes), or s (seconds), you can use a number followed by the letter for the format string, such as 5m.

SELECT DISTINCT DATETIME(starttime, 'HH:mm', '5m'), count(*) FROM usersession;

counts sessions in five-minute time blocks.

DATETIME indexes and calculated persisting DATE columns.

Example 1: Let's use plain DISTINCT clause on DATETIME column casted to a DATE:

SELECT  COUNT(date)
FROM    (
SELECT  DISTINCT CAST(CAST(CAST(dtcol AS FLOAT) AS INTEGER) AS DATETIME) AS date
FROM    [20090820_distinct].t_datetime
)

Example 2: SELECT DISTINCT from a date/time column only the date. (MM/DD/YYYY). Then I want to be able to requery the generated data from that view using BETWEEN. So, in order to save time, I'm utilising the Convert function (if anyone knows of a better solution, please let me know):

SELECT DISTINCT Order_id, CONVERT(char(10),
Customer_Order_table.date_order_placed, 101) AS
order_date;

Example 1: Get distinct empid's from 3 tables Emp1,Emp2,Emp3.

select distinct empID from
(select empid from emp1
union select empid from emp2
union select empid from emp3) as emp

Example for distinct from multiple tables:

Select Distinct EmpID from Emp1
UNION
Select Distinct EmpID from Emp2
UNION
Select Distinct EmpID from Emp3

Example 2: Create SQL code that takes the distinct values from two columns and “appends” them. By that, I mean that that the following table:

I'm implementing this with a union from two tables. Therefore, the goal is to combine all unique account numbers in both  tables with all unique publication numbers in both databases. 

I also want a constraint that restricts both tables to orders placed more than a year ago.

Select Distinct 
 "Tra.".cus_outnum As "account number", 
 "Tra.".artnum As "article number"
From 
 (table1) "Tra." 

Union

Select Distinct 
 "Sal.".outnum As "account number", 
 "Sal.".artnum As "article number"
From 
 (table2) "Sal."

The main distinction between the DISTINCT and GROUP BY operators is that the GROUP BY operator is for aggregating or grouping data, whilst DISTINCT is only for getting distinct values. However, similar to SQL's DISTINCT, it's a little more complicated.

When there is no aggregate function, the group produces the same outcome as distinct.

In SQL, the DISTINCT clause is used to filter out duplicate records and return only unique ones. However, if you aren't aggregating data, DISTINCT is sometimes easier to write and read.

GROUP BY is also used to fetch unique items, but it is mostly used for data aggregation and row grouping.

Example 1: Remove duplicates with more than one GROUP BY column by using DISTINCT. This is, of course, another example:

SELECT DISTINCT actors, COUNT(*)
FROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id)
GROUP BY actors, id;

Output:

Example 2: MySQL query that that uses both DISTINCT and GROUP BY together:

SELECT  DISTINCT user_id, post_id, post_content
FROM  some_table
GROUP BY  post_id, user_id
HAVING  post_content LIKE '%abc%';

Here is a scenario to go along with the query: Each user has a unique id, user_id, and can make multiple posts which are identified by a unique id, post_id. Each post would contain some text.

Example 3: Consider the following Students table having duplicate records:

Query:

select DISTINCT grp_a, grp_b
from   t
GROUP BY grp_a;

To delete duplicates from several fields in your SELECT statement with a WHERE clause, use the SQL Server DISTINCT clause.

We're setting a criterion in place that tells MySQL to return the result set's unique rows. When we combine the LIMIT clause with a DISTINCT clause in MySQL queries, we're essentially telling the server how many unique rows of the result set should be returned.

Example 1: On the table called "testing," we may use the WHERE and LIMIT clauses with DISTINCT as follows :

Select * from testing;

Select DISTINCT Lname from testing where Lname IS NOT NULL limit 3;

Output:

Example 2:

SELECT DISTINCT first_name, last_name
FROM employees
WHERE employee_id >=50
ORDER BY last_name;

This SQL Server DISTINCT clause instance returns every distinct first name and last name match from the employees table with an employee id greater than or equal to 50. The results are ordered by last name in ascending order.

In this scenario, the separate keyword applies to each field given after it, resulting in distinct combinations.

Example 3: SELECT/DISTINCT statement with WHERE clause to obtain just those unique employees whose paid pay is more than or equal to 4500. Look at the query and the result-set:

SELECT DISTINCT(emp_name) FROM sto_emp_salary_paid
WHERE emp_sal_paid >= 4500;

A statement containing both DISTINCT and ORDER BY would need two different sorting procedures if it didn't use a transformation: one to fulfil DISTINCT and the other to fulfill ORDER BY.

The requirements are:

The ORDER BY list must contain a subset of the SELECT list's columns.

The ORDER BY list's columns are sorted in ascending order.

It is not necessary to create a unique index.

Example 1: The issue is that the DISTINCT does not specify the columns used in the ORDER BY. To accomplish this, you'll need to sort on an aggregate function and use a GROUP BY to enable the DISTINCT work.

SELECT DISTINCT Category, MAX(CreationDate) 
FROM MonitoringJob 
GROUP BY Category 
ORDER BY MAX(CreationDate) DESC, Category;

Example 2:

SELECT DISTINCT miles, meal
FROM Flights
ORDER BY meal;

Example 3: Perform a SELECT DISTINCT statement that should sort by a column that isn't in the SELECT list.

SELECT DISTINCT CustomerID
FROM Northwind.dbo.Orders
ORDER BY OrderID

The preceding example tries to sort by OrderID. Because the term DISTINCT is also supplied, either the column OrderID or the resultset must be sorted by CustomerID.

Example 4: The following statement uses the ORDER BY clause to choose unique values in the bcolor column from the t1 database and sort the result set in alphabetical order.

SELECT
DISTINCT bcolor
FROM
distinct_demo
ORDER BY
bcolor;

Example 1: Maintain the one with the highest id and eliminate the duplicate rows with the lowest ids.

You only need to switch the operator in the WHERE clause to retain the redundant rows with the lowest id:

DELETE  FROM
 basket a
   USING basket b
WHERE
   a.id > b.id
 AND a.fruit = b.fruit;

Example 2: The ROW_NUMBER function that was introduced in Microsoft SQL Server 2005 makes this operation much simpler:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
 PARTITION BY key_value
 ORDER BY (SELECT NULL)
)
FROM original_table
) AS T
WHERE DupRank > 1 

This script takes the following actions in the given order:

The data is partitioned using the ROW_NUMBER function based on the key value, which can be one or more columns separated by commas.

It removes any records with a DupRank greater than one. Duplicate records are indicated by this value.

Example 3: SQL to Remove Duplicates without Distinct used to Remove Duplicates Using Row_Number:

WITH CTE (Col1, Col2, Col3, DuplicateCount) AS 
( SELECT Col1, Col2, Col3, ROW_NUMBER() OVER
(PARTITION BY Col1, Col2, Col3 ORDER BY Col1) AS DuplicateCount FROM MyTable ) 
SELECT * from CTE Where DuplicateCount = 1.2.

Remove Duplicates using self Join. Remove Duplicates using group By.

SELECT
 OfficeID
FROM (SELECT *
 ,ROW_NUMBER() OVER (PARTITION BY OfficeID ORDER BY TempID) SortOrder
 FROM @TempTable) AS t
WHERE t.SortOrder = 1
ORDER BY TempID;

To calculate the sum of a field in distinct records, use the SUM function with distinct.

The aggregate function SUM() returns the total of a set of numbers.

The sum of all values or individual values Only the numeric column can be used with the SUM function.

The SUM() function will only return the sum of distinct values if you specify the DISTINCT option. When you use the ALL option, the SUM() method adds all values together, including duplicates. ALL is the default setting.

To calculate the sum of unique values, you use the DISTINCT operator e.g., the SUM(DISTINCT) of the set (1,2,3,3,NULL) is 6.

Syntax:

SELECT SUM(distinct expression )
FROM tables
WHERE predicates;

Example 1:

SELECT SUM(distinct salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

Example 2: use the DISTINCT option in the SUM() function to calculate the sum of distinct values.

SELECT 
SUM(DISTINCT c1) total_distinct
FROM 
sum_demo;

Output:

Example 3:

WITH Dist
 AS (SELECT DISTINCT LineName,
 Material,
 ProdQty
 FROM   [table])
SELECT LineName,
 COUNT(Material) AS Cmat,
 SUM(ProdQty)    AS ProdQty
FROM   Dist
GROUP  BY LineName;

In a column, the functions DISTINCT and TOP are used. It can't be used on numerous columns, either.

Example 1: Then compare the results from the last two queries using SQL DISTINCT or TOP 10 operations.

It's worth noting that the query "DISTINCT TOP 10" includes the first 10 entries from the "DISTINCT" query.

We can deduce that a DISTINCT list is generated first, followed by the TOP 10 entries.

SELECT   DISTINCT TOP 10 FirstName, LastName
FROM     Person.Person
ORDER BY LastName;

And you’ll see it returns first 10 unique first and last names as sorted by LastName.

Example 2:

SELECT DISTINCT TOP 10
 First_Name
FROM
 EmployeeDb
ORDER BY
First_Name

Example 3: Unique list ordered by LastName

SELECT   DISTINCT TOP 10 FirstName, LastName
FROM     Person.Person
ORDER BY LastName;

You'll notice that it returns the first ten unique first and last names, sorted by LastName.

The first 10 rows are arranged by LastName.

Compare the results from the last two searches to see which occurs first: the DISTINCT or TOP 10 procedures.

It's worth noting that the query "DISTINCT TOP 10" includes the first 10 entries from the "DISTINCT" query.

We can deduce that a DISTINCT list is formed initially, followed by the TOP 10 entries.

The distinction between Union and Union All is that UNION ALL does not remove duplicate entries; instead, it simply gathers all rows from all tables that match your query criteria and merges them into a single table.

On the results set, a UNION statement effectively does a SELECT DISTINCT.

The output will be equivalent to the Union result set if you select Distinct from the Union All result set.

If you use UNION ALL explicitly, the result will include duplicate rows if they exist. UNION ALL is quicker than UNION DISTINCT since it does not have to deal with duplicates.

Example 1: Let me explain with Example:

I have two queries. one is Union another one is Union All

SET STATISTICS TIME ON
GO
 
select distinct * from (select * from dbo.user_LogTime
union all
select * from dbo.user_LogTime) X 
GO

SET STATISTICS TIME OFF

SET STATISTICS TIME ON
GO
 
select * from dbo.user_LogTime
union
select * from dbo.user_LogTime
GO

SET STATISTICS TIME OFF

Example 2:

SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2;

Output:

Example 3: eliminate duplicates using DISTINCT:

SELECT DISTINCT * FROM
(     
 SELECT col1,col2,col3 FROM Table1 
 UNION ALL SELECT col1,col2,col3 FROM Table2 
 UNION ALL SELECT col1,col2,col3 FROM Table3 
 UNION ALL SELECT col1,col2,col3 FROM Table4 
 UNION ALL SELECT col1,col2,col3 FROM Table5       
 UNION ALL SELECT col1,col2,col3 FROM Table6       
 UNION ALL SELECT col1,col2,col3 FROM Table7       
 UNION ALL SELECT col1,col2,col3 FROM Table8       
)

When you use DISTINCT in an INNER JOIN query that returns more than one column in the SELECT, instead of utilising an index, a full table search is done. When the SELECT returns only one column, the index is used.

Example 1:

SELECT DISTINCT a.col1, a.col2
FROM tab1 a INNER JOIN tab2 b
ON a.col1 = b.col1
WHERE b.col2 in ('value1', 'value2')
ORDER BY a.col1;

Example 2:

select distinct a.FirstName, a.LastName, v.District
from AddTbl a 
inner join ValTbl v
on a.LastName = v.LastName
order by a.FirstName;
Or this (it does the same, but the syntax is different):

select distinct a.FirstName, a.LastName, v.District
from AddTbl a, ValTbl v
where a.LastName = v.LastName
order by a.FirstName;

Example 3: Create a query to display four columns (from distinct tables) with no duplicates in the first column. Even though the records in that table are distinct, the query I wrote returns duplicate records in the first column. My question is as follows:

select distinct vs.policy_no, bes.entity_no, bec.eff_from, vs.level_cd from
Vitality_status vs
left join benefit_entities bes on vs.policy_no = bes.policy_no
left join benefit_entity_compos bec on bes.benefit_entity_id=bec.benefit_entity_id
left join policy_trns pt on vs.policy_no = pt.policy_no
where
 ((sysdate between vs.eff_from and vs.eff_to) and (vs.level_cd>2))
and
 ((bec.eff_from < to_date(20080101,'YYYYMMDD')) and (bec.compo_id='VIRACT'))
and
 ((pt.prod_cd='VITA') and (pt.internal_co=11) and (pt.eff_date=to_date('20090101','YYYYMMDD')))

The DISTINCT clause in SQL does not discard NULL values. It indicates that the two NULLs are identical. If the SELECT statement returns NULLs, the DISTINCT statement simply gives one NULL.

A null value is assumed to be duplicated when using the DISTINCT keyword. When DISTINCT is used in a SELECT statement, no matter how many null values are discovered, only one NULL is returned in the results.

When you use the DISTINCT clause on a column with NULL values, the DISTINCT clause will only store one NULL value since it considers all NULL values to be the same.

Example 1: The following SELECT statement selects all the regions from the region column of the employees table:

SELECT region FROM employees;

The query returns duplicate regions, as you can see from the result. NULL, for instance, occurs four times.

The DISTINCT operator is used in the following SELECT statement to select unique regions from the employees table:

SELECT DISTINCT region
FROM employees;

The result set now has unique regions.

Example 2: In the customers table, the state column has NULL values.

When you query the states with the Separate clause, you'll find distinct states and NULL as seen below:

SELECT DISTINCT state
FROM customers;

Output:

Text of the query is less likely to be modified if you supply an alias to the virtual table:

SELECT [ TOP integer-value ]
{ * | [ ALL | DISTINCT ] { column-name | selection-expression } [AS alias] [,...] }
FROM { table-reference } [ join-clause ]...
[WHERE [NOT] boolean-expression [ {AND | OR} [NOT] boolean-expression]...]
[clause...]

table-reference:

{ table-name [AS alias] | view-name [AS alias] | sub-query AS alias }

The SELECT clause with a single asterisk * gives the list of fields for each event.

Example: Specify a comma-separated list of fields to return particular fields for each occurrence. The AS construct can be used to alias each field expression to a label.

SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName) AS a