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/
/// RESOURCES
๐ Get my free SQL Cheat Sheets: https://www.databasestar.com/get-sql-cheat-sheets/
๐งญ SQL Roadmap from basics to advanced: https://www.databasestar.com/sql-roadmap/
โ๏ธ Learn how to design a database: https://databasestar.mykajabi.com/edd
๐ Improve your SQL and database skills with Database Star Academy: https://www.databasestar.com/dsa/
๐ง Contact me here: https://www.databasestar.com/contact/
๐ Database Design Exercises: https://bbrumm.gumroad.com/l/hqsxi
๐ SQL Practice Questions: https://bbrumm.gumroad.com/l/ulekqk
๐ SQL Performance Quick Guide: https://bbrumm.gumroad.com/l/fcvny
๐ Ultimate SQL How-To Guide: https://bbrumm.gumroad.com/l/oqggau
2 Comments