FILTER AFTER ORDER BY CLAUSE IN ORACLE

FILTER AFTER ORDER BY CLAUSE IN ORACLE

Sequence of Commands in Oracle

In SQL the order of commands are:

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

Usually ORDER BY is the last command in SQL. But 11g has introduced a FETCH command which will be used after ORDER BY clause. So we can filter out data without using an outer query.

We will use the below data set for this article:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID 
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

We want to extract the top 3 earner of department id 30 of employees table.

Without FETCH we were writing code earlier:

SELECT * FROM(
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, ROWNUM RN
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY SALARY DESC)
WHERE RN<= 3;

With FETCH

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, ROWNUM RN
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY SALARY DESC
FETCH FIRST 3 ROWS ONLY;

OFFSET CLAUSE:

OFFSET clause helps us in extracting any particular records or a range of particular records.

Syntax:

OFFSET { integer-literal | ? } {ROW | ROWS}

FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

Now we want to extract the 3rd highest earner of Department 30.

Without FETCH

SELECT * FROM(
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, ROWNUM RN
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY SALARY DESC)
WHERE RN = 3;

With FETCH & OFFSET

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID, ROWNUM RN
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY SALARY DESC
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY;

Complete list of Department id 30 based on descending order of hire date:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE DESC;

Now lets say we want to extract latest 3 employees joined in Department 30:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
ORDER BY HIRE_DATE DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY;

Now lets say we want to extract last 50 employees of entire Employees table based on Employee Id:

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
OFFSET (SELECT COUNT(1) FROM EMPLOYEES) - ((SELECT COUNT(1) FROM EMPLOYEES)-50) ROWS
FETCH NEXT 50 ROWS ONLY;

In the above example we can see that we have extracted last 50 records of Employees table. Also we have offset 67 records before extracting last 50 records. We have dynamically removed the 67 rows from total count 107 to get the count of 50.

So FETCH command in SQL is a useful command. It helps to write complex queries with ease.

OTHER SQL TOPICS:

Object Privileges For Views In Oracle
Object Privileges For Table In Oracle
UNION Operators In Oracle
INTERSECT Operator In Oracle
INDEXES in Oracle
SYNONYMS in Oracle