Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » MiscellaneousRSS Feeds

Eliminating Duplicate Rows with DISTINCT

Posted By: Sarita Patel     Category: SQL Server     Views: 4296

This article specifies the significance of DISTINCT keyword in sql sever.

DISTINCT keyword is used to eliminate duplicate rows from a result.

To eliminate duplicate rows :

Syntax of using DISTINCT keyword in select clause :

SELECT DISTINCT columns

FROM    table

 


Example of without using DISTINCT keyword in select clause :

SELECT Country, City

FROM   Suppliers

ORDER BY Country, City

 

Output

Country         City

Australia        Melbourne

Australia        Melbourne

Brazil           Sao Paulo

Canada          Montréal

Canada          Ste-Hyacinthe

Finland          Lappeenranta

France           Montceau

France           Montceau

France           Annecy

 

As you can see that above query returns multiple rows having duplicate value. Country and city columns having value 'australia and melbourne' and 'France and montceau' are displaying twice in a result. You can eliminate duplicate value by below example.

 


Example of using DISTINCT keyword in select clause : 

SELECT DISTINCT Country, City

FROM    Suppliers

ORDER BY Country, City

 

Output

Country         City

Australia        Melbourne

Brazil           Sao Paulo

Canada          Montréal

Canada          Ste-Hyacinthe

Finland          Lappeenranta

France           Montceau

France           Annecy

 

Using distinct keyword, duplicate rows from result are eliminated. Country and city columns having value 'australia and melbourne' and 'France and montceau' are displaying only once in a result.

  
Share: 


Didn't find what you were looking for? Find more on Eliminating Duplicate Rows with DISTINCT Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of Eliminating Duplicate Rows with DISTINCT is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
Shruti Sharma from United States Comment on: Mar 31

DELETE
FROM TableName
WHERE ColumnId NOT IN
(
SELECT MAX(ColumnId)
FROM TableName
GROUP BY LTRIM(RTRIM(ColumnDuplicate)))


Here,
TableName - Table name from which you want to remove duplicate rows.
ColumnId - Primary Key column which has unique value.
ColumnDuplicate - It is Column name which contains duplicate values.


Example: Following example will delete all rows with duplicate title in Forum table.

DELETE
FROM Forum
WHERE ForumId NOT IN
(
SELECT MAX(ForumId)
FROM Forum
GROUP BY LTRIM(RTRIM(ForumTitle)))

Lintin Maryson from India Comment on: Mar 31
good point...
Will you please tell me the syntax for removing duplicate values without using distinct keyword.... waiting for your kind replay..
thank you...........

View All Comments