vb.net 2d barcode dll RETRIEVAL: SOME ADVANCED FEATURES in Java

Maker DataMatrix in Java RETRIEVAL: SOME ADVANCED FEATURES

Listing 9-24. Ranking employee salary using multiple table access SELECT e1.deptno, e1.ename, e1.msal, (SELECT COUNT(1) FROM employees e2 WHERE e2.msal > e1.msal)+1 sal_rank FROM employees e1 ORDER BY e1.msal DESC; DEPTNO -----10 20 20 20 30 10 30 30 10 30 30 20 30 20 ENAME MSAL SAL_RANK -------- ------ -------KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 4 BLAKE 2850 5 CLARK 2450 6 ALLEN 1600 7 TURNER 1500 8 MILLER 1300 9 WARD 1250 10 MARTIN 1250 10 ADAMS 1100 12 JONES 800 13 SMITH 800 13

This version of the query doesn t use an analytical function. It uses a more traditional, subquerybased approach to the problem of ranking. The problem is that the subquery essentially represents an additional query to the employees table for each row that is being ranked. If the employees table is large, this can result in a large number of data reads and consume minutes, perhaps hours, of response time. Listing 9-25 generates the same report using the analytical function RANK. Listing 9-25. Ranking employee salary using analytical funcions SELECT e1.deptno, e1.ename, e1.msal, RANK() OVER (ORDER BY e1.msal DESC) sal_rank FROM employees e1 ORDER BY e1.msal DESC; DEPTNO -----10 20 20 20 30 10 30 30 10 ENAME MSAL SAL_RANK -------- ------ -------KING 5000 1 FORD 3000 2 SCOTT 3000 2 JONES 2975 4 BLAKE 2850 5 CLARK 2450 6 ALLEN 1600 7 TURNER 1500 8 MILLER 1300 9

30 30 20 30 20

1250 1250 1100 800 800

10 10 12 13 13

Using the analytical function creates a statement that is simpler and self documenting. Figure 9-2 illustrates the basic format of the analytical function.

Figure 9-2. Basic syntax for analytical functions The use of the term OVER indicates an analytical function, something you need to keep in mind as there are analytical functions with the same names as regular functions. For example, the analytical functions SUM and AVG have the same names as their non-analytical counterparts. A key clause is ORDER BY. This indicates the order in which the functions are applied. In the preceding example, RANK is applied according to the employee salary. Remember that the default for ORDER BY is ascending, smallest to largest, so you have to specify the keyword DESC, for descending, to sort from largest to smallest. The ORDER BY clause must come last in the analytical function.

Do take care to remember that the statement ORDER BY and the function ORDER BY are independent of each other. If you place another clause after the ORDER BY in a function call, you get the following rather cryptic error message:

The ORDER BY in a function call applies only to the evaluation of that function, and has nothing to do with sorting the rows to be returned by the statement.

A partition is a set of rows defined by data values in the result set. The default partition for any function is the entire result set. You can have one partition clause per function, though it may be a composite

partition, including more than one data value. The PARTITION BY clause must come before the ORDER BY clause. Figure 9-3 illustrates the basic format of the analytical function using a PARTITION.

Figure 9-3. Analytical function partitioning syntax When a partition is defined, the rows belonging to each partition are grouped together and the function is applied within each group. In Listing 9-26, one RANK is for the entire company and the second RANK is within each department. Listing 9-26. Ranking employee salary within the company and department SELECT e1.deptno, e1.ename, e1.msal, RANK() OVER (ORDER BY e1.msal DESC) sal_rank, RANK() OVER (PARTITION BY e1.deptno ORDER BY e1.msal DESC) dept_sal_rank FROM employees e1 ORDER BY e1.deptno ASC, e1.msal DESC; DEPTNO -----10 10 10 20 20 20 20 20 30 30 30 30 30 30 ENAME MSAL SAL_RANK DEPT_SAL_RANK -------- ------ -------- ------------KING 5000 1 1 CLARK 2450 6 2 MILLER 1300 9 3 FORD 3000 2 1 SCOTT 3000 2 1 JONES 2975 4 3 ADAMS 1100 12 4 SMITH 800 13 5 BLAKE 2850 5 1 ALLEN 1600 7 2 TURNER 1500 8 3 MARTIN 1250 10 4 WARD 1250 10 4 JONES 800 13 6

Functions cannot span a partition boundary, which is the condition where the partition value changes. When the deptno changes value, the RANK() with the PARTITION BY e1.deptno resets to 1. Other functions, such as LAG or LEAD, cannot reference rows outside the current row s partition. Listing 9-27 shows how to reference data in rows other than the current row.