Description: RANK is an analytic function that returns the rank of a value in a group of values. It is very similar to the dense_rank function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings.
Syntax #2 - Used as an Analytic Function
As an Analytic function, the rank returns the rank of each row of a query with respective to the other rows.
Language: PL/SQL Highlight Mode: PLSQL Last Modified: March 04th, 2009
RANK(<rank expression>) WITHIN GROUP(ORDERBY<expression><ASC|DESC> NULLS <FIRST|LAST>)-- Syntax #1 - Used as an Aggregate Function-- As an Aggregate function, the rank returns the rank of a -- row within a group of rows. The syntax for the rank function -- when used as an Aggregate function is:RANK( expression1,... expression_n ) WITHIN GROUP(ORDERBY expression1,... expression_n )-- expression1 .. expression_n can be one or more expressions -- which identify a unique row in the group.-- The SQL statement below would return the rank of an employee -- with a salary of $1,000 and a bonus of $500 from within the -- employees table.SELECTRANK(1000,500) WITHIN GROUP(ORDERBY salary, bonus)FROM employees;-- The syntax for the rank function when used as an Analytic -- function is:RANK() OVER ([ query_partition_clause]ORDERBY clause )SELECT employee_name, salary,RANK() OVER (PARTITIONBY department ORDERBY salary)FROM employees
WHERE department ='Marketing';
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.