Top 5 SQL Queries

   


Remove duplicates
DELETE from emp WHERE rowid NOTIN ( SELECT max(rowid) from emp GROUPBY empno);


Display only duplicated
SELECT deptno from emp GROUPBY deptno HAVING count(*)>1


Top 5 salaries
SELECT empno, ename, sal from (SELECT * from emp ORDERBY sal DESC) WHERE rowno<6


Only 5th row
SELECT * from emp a WHERE 5=(SELECT count (rowid) from emp b WHERE a.rowid>=b.rowid);


5th max sal
SELECT distinct from emp a WHERE 5= (SELECT count (DISTINCT sal) from emp b WHERE b.sal>=a.sal;