SQL Query ideas to eliminate duplicates

You can eliminate duplicates by two methods. I am sharing those for your quick reference.

Ideas to Remove Duplicates

Idea-1: Removing Duplicates Using GROUP BY

Select * from samples.loan_account group by accno;

Idea-2: Removing Duplicates Using DISTINCT

select distinct accno from samples.loan_account;

Advantages and Disadvantages

There are some advantages and dis-advantages in the above methods. The method GROUP BY will takes less CPU time. The method DISTINCT will take more CPU time (an internal sort is required before completing SQL).

Complete Syntax to Use Full Select in SQL Query

SELECT E1.EMPNO, E1.LASTNAME,E1.SALARY E1.DEPTNO, (SELECT AVG(E2.SALARY)
FROM EMP E2WHERE E2.DEPTNO = E1.DEPTNO)
AS DEPT_AVG_SALFROM EMP E1 
ORDER BY E1.DEPTNO, E1.SALARY

Explanation

To add the average to the result, the subquery must be repeated as a scalar full select in the SELECT clause.

Scalar full selects were introduced in DB2 Version 8 and may be used as an expression anywhere in the statement provided they only return 1 column and 1 row.

Summary

  • Often times, there are multiple ways of getting the same answer in programming. This applies to SQL too.
  • Everyone needs to be stronger in SQL.
  • Strong SQL skills greatly enhance one’s ability to do performance tuning of queries, programs, and applications.