Databases Reference
In-Depth Information
We can also use the
DENSE_RANK()
function, as in step 5. You can see the output
(an excerpt) in the following screenshot:
In this case, it returns 3853 rows that satisfy the
WHERE
condition. This example is useful
to understand the difference between the
RANK()
and
DENSE_RANK()
functions. Both
the functions assign a ranking to each row based on the
ORDER
BY
clause; they assign the
same rank if the rows have the same value—in our example on the
CUST_YEAR_OF_BIRTH
field. But when doing so, the
DENSE_RANK()
function doesn't increment the counter used
to assign the rank. So after 100 rows with the same rank, the next rank assigned will be the
previous rank + 1, while the
RANK()
function will assign—under the same conditions—the
previous rank + 100.
In step 7, step 8, and step 9, we execute the same queries, as mentioned earlier, to view
the execution plan.