Query Examples [Delete Duplicate, Top-N Analysis, Alternate Records]

SQL Query to delete duplicate records

delete from t2 a
where a.rowid > (select min(rowid)
from t2 b
where a.c1 = b.c1)

delete from t2 a
where a.rowid not in (select max(rowid)
from t2 b
where a.c1 = b.c1)

Query to select nth highest row in the table:
1] select a.sal
from emp a
where (&n-1) = (select count(distinct(b.sal))
from emp b
where b.sal > a.sal)
Oracle 10g new feature : Handling of quotes in string
insert into emp
values (1234, q'!Arpit'sdfs!', 'Engg', 7902, '24-feb-1968',2334, 12 , NULL)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
2] SELECT * FROM ( SELECT deptno, ename, sal, DENSE_RANK() OVER ( PARTITION BY deptno ORDER BY sal desc ) TopN FROM emp)
WHERE TopN <= 3ORDER BY deptno, sal DESC/

3] SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp)
WHERE Top3 <= 3 ;

[Gives only first 3. Duplicates r counted as 2]

Query to select alternate records from table

SELECT *
FROM emp
WHERE (rowid,1) IN (SELECT rowid,mod(rownum,2) FROM emp);

SELECT * FROM ( SELECT rownum r, emp.* FROM emp ) temp WHERE MOD(temp.r,2) = 1 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1234 Arpit'sdfs Engg 7902 24-FEB-68 2334 12
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10