Some cool and useful SQL queries

There are some queries I came across many time while interacting with database. I have compiled some of those. I have tested these queries on Oracle 11g database. I hope it will help you too.

Note: If you want to create table structure and populate with the data used in these examples, then you can use the sql file from here.

• Record Ranking

Record ranking is as its name suggests provides rank to the records fetched. Till now I came across 3 approaches.

Row Number- Provides natural numbers to each row. It could be called as Ordinal ranking in laymans term

SELECT
  ROW_NUMBER() OVER (ORDER BY sal DESC) salary_rank,
  empno,
  ename
FROM emp;

Rank - Honors same rank to all the records with same value but follows Standerd competition ranking

SELECT

  RANK() OVER (ORDER BY sal DESC) salary_rank,  empno,sal,enameFROM emp;

Dense Rank - Follows Dense ranking system

SELECT  DENSE_RANK() OVER (ORDER BY sal DESC) salary_rank,  empno,sal,enameFROM emp;

• Top n analysis: 

In many cases its required to know first few records based on some criteria, like first 5 employees with highest salery, or most experienced 10 employees. Top n analysis is handy in such cases.

SELECT ROWNUM,  ENAME, EMPNO, SAL FROM   (SELECT *    FROM EMP    ORDER BY SAL DESC) E1 WHERE rownum<4;

This query provides top 3 employees with highest salary.

• Nth record

Similar to top n analysis, this query provides employee with perticular ranking.

Following query return employee with 3rd highest salary

select * from (select row_number() over (order by sal desc) salary_rank, emp.* from emp) where salary_rank=3;

• Top Nth item per group

This is special case of nth record query. Of the example could be employees with third highest salary in each department.

WITH temp_emp AS   ( SELECT empno,deptno
           ename,            row_number() over (partition BY deptno                               ORDER BY sal DESC) AS rownm    FROM emp ) SELECT * FROM temp_emp WHERE rownm =3;

• Hierarchical/Recursive Query

This one is  my favourite. It shows power of sql.

This approach is useful when your table contains hierarchical data.

Take an example of our favourite employee table. This table contains employee and his manager. Manager himself is employee and could report to his manager. This hierarchy goes on till CEO of the company.

If you have a requirement where you need to show all the employees falling under hierachy of employee 7566 then following query can do the trick.

WITH VIR_EMP(EMPNO,MGR) AS   ( SELECT EMPNO,MGR    FROM EMP    WHERE MGR=7566    UNION ALL SELECT TEMP_EMP.EMPNO,TEMP_EMP.MGR    FROM EMP TEMP_EMP,VIR_EMP    WHERE TEMP_EMP.MGR=VIR_EMP.EMPNO ) SELECT EMPNO,        MGR FROM VIR_EMP;

• No of records with same data/ duplicate records with count

SELECT emp1.empno,        emp1.sal,        cnt FROM emp emp1 INNER JOIN   (SELECT sal,           count(sal) cnt    FROM emp    GROUP BY sal) emp2 ON emp1.sal=emp2.sal;

• Remove Duplicate records but leave one

This one could be really useful for cleaning up the duplicate records.

DELETE FROM emp WHERE empno NOT IN     (SELECT MIN (empno)      FROM emp      GROUP BY deptno);