Oracle DENSE_RANK Function

https://www.databasestar.com/oracle-dense_rank/
The Oracle DENSE_RANK function allows you to calculate a rank of a row in a group of rows. It returns this rank as a NUMBER value.

This function can be used as either an analytic or an aggregate function, and the syntax depends on how you use it.

As an aggregate function it looks like this:

DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] )

These parameters are:
- expr: this is the value to search for in a particular table. You can specify multiple search values, and just separate them by a comma.
- order_expr: this is used to order the expr values by, because you get a different results depending on what order you rank the values in.
- NULLS FIRST or LAST: this is where you can specify if you want to have NULL values appear at the top of your ranking order or at the bottom.

As an analytic function, it looks like this:

DENSE_RANK() OVER ( [query_partition_clause] order_by_clause)

The parameters are:
- query_partition_clause: this is the expression that the ranking is “grouped” by, as analytic functions allow you to group data within rows.
- order_by_clause: this is the expression to order your results by to determine a rank.

For this function, if two records have the same sort or rank position, they will have the same RANK value. It won’t cause a gap in the rankings, unlike the RANK function which does cause a gap.

For more information about the Oracle DENSE_RANK function, including all of the SQL shown in this video and the examples, read the related article here:
https://www.databasestar.com/oracle-dense_rank/