DISTINCT is useful in certain circumstances, but it has drawback that it can increase load on the query engine to perform the sort (since it needs to compare the result set to itself to remove duplicates)
Below are alternate solutions :
1. 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
emp_name emp_address sex matial_status uuuu eee m s iiii iii f s uuuu eee m s
SELECT emp_name, emp_address, sex, marital_status from YourTable a WHERE NOT EXISTS (select 1 from YourTable b where b.emp_name = a.emp_name and b.emp_address = a.emp_address and b.sex = a.sex and b.create_date >= a.create_date)
3. Remove Duplicates using group By
The idea is to group according to all columns to be selected in output. For example, if we wish to print unique values of “FirstName, LastName and MobileNo”, we can simply group by all three of these.
SELECT FirstName, LastName, MobileNo FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo;
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Delete Duplicates in MS SQL Server
- Find Duplicates in MS SQL Server
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between SQL and T-SQL
- Swap two numbers in PL/SQL without using temp
- SQL | Distinct Clause
- Distinct clause in MS SQL Server
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL | SELECT Query
- SQL | WHERE Clause
- SQL | AND and OR operators
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement
- SQL | SELECT TOP Clause
- SQL | ORDER BY
- SQL | Aliases
- SQL | Wildcard operators
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : Subha Saha