Databases Reference
In-Depth Information
It's also possible to look further ahead or back in the result set.
LAG
and
LEAD
have
two additional optional parameters. One parameter is for the offset of number of
records. The third parameter provides a default value in case
LAG
or
LEAD
points
outside the window.
As stated before, the
LAG
and
LEAD
function can also include the
IGNORE NULLS
clause,
allowing you to skip over the
NULL
values and return the last
NOT NULL
value.
Ranking—top N
Showing the top 3 of each department is a breeze with analytic functions. What
we need to do is create partitions and assign a rank to each row within the
partition. There are three variants to the ranking function—
RANK
,
DENSE_RANK
,
and
ROW_NUMBER
. These functions assign numbers based on the
ORDER BY
clause
within each partition. They all do it a little bit differently.
The difference lies in the way equality is resolved.
RANK
allows ranking numbers to
be skipped.
DENSE_RANK
uses a different kind of ranking, which doesn't skip any
number.
ROW_NUMBER
assigns an arbitrary number to each row when it is not possible
to resolve
ORDER BY
of the windows clause, comparable to the way
ROWNUM
assigns a
value to a row.
Take a look at the following example. You can see the differences between types of
ranking. When we look at department
20
more closely, you can see that there is a
tie for irst place: both have
3000
under the
SAL
column. The second highest salary
(
2975
) has the
RANK
value of
3
, while for
DENSE_RANK
, the value is
2
.
RANK
skipped
second place altogether—we do not award a silver medal if we already have two
gold medalists. In the last column, where
ROW_NUMBER
is used, a value is assigned
arbitrarily to the highest paid employees.
SQL> select ename
2 , deptno
3 , sal
4 , rank() over (partition by deptno
5 order by sal desc
6 ) rk
7 , dense_rank() over (partition by deptno
8 order by sal desc
9 ) dr
10 , row_number() over (partition by deptno
11 order by sal desc
12 ) rn
13 from emp
14 where deptno = 20
15 order by deptno
