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
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.
- 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.