You can eliminate (Remove) duplicate rows by using two methods. Those are GROUP BY and DISTINCT. I have shared two ideas with examples that you can use in your projects.
Ideas to Remove Duplicates
Idea-1: Use GROUP BY to Remove Duplicates.
Select *
from samples.loan_account
group by accno;Idea-2: Use DISTINCT to Remove Duplicates.
select distinct accno
from samples.loan_account;Advantages and Disadvantages
There are some advantages and disadvantages 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_SAL
FROM 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.
Keep Reading
Srinimf 