SQL has traditionally been a poor tool for analysing data in context. ie. Selecting information about a row that requires input from neighboring rows. For instance, to select information about the highest salary earner in each department, we must first find the employees with the highest salary, and then select their details:
SELECT * FROM emp WHERE (dept_no, sal) in ( SELECT dept_no, max(sal) FROM emp GROUP BY dept_no)
Queries that are context sensitive have one thing in common: they must refer to the same table more than once. The example above is remarkably simple; more complex queries must access the same table 3, 4 or more times.
Oracle 8i has supplied a new feature called Analytic Functions which are unique in SQL: they allow a row to access information from its neighbors without having to perform a self-join. Analytic functions are difficult to get the hang of, but once mastered are quite easy and even intuitive! See the Oracle SQL Reference for more information on Analytic Functions.
The query above can be re-written with analytic functions as follows:
SELECT * FROM ( SELECT a.* , max(sal) OVER(PARTITION BY dept_no) AS max_sal FROM emp a ) WHERE sal = max_sal
Be aware that Analytic Functions are not necessarily quicker than self joins. Even though they seem to perform fewer and more simple steps, queries like the one above can be more efficient when written as a self join. Always trial both option to find the fastest.
If only a single result is required (eg. One employee with highest salary, rather than all with that salary) then it is even faster to use the
KEEP modifier to the aggregate function.
SELECT max(ename) KEEP (DENSE_RANK LAST ORDER BY sal), max(sal) FROM emp a
Another option is to process the data in a procedural language such as PL/SQL. eg.
DECLARE CURSOR c1 IS SELECT * FROM emp ORDER BY dept_no, sal prev_row c1_rec%rowtype; BEGIN FOR this_row IN c1 LOOP IF (prev_row.dept_no IS NULL) THEN prev_row := this_row; END IF; IF (this_row.dept_no != high_row.dept_no) THEN do_whatever(high_row); END IF; END LOOP; IF (high_row.dept_no IS NOT NULL) THEN do_whatever(high_row); END IF; END;