Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: RANK Jump to:  
Category: >> Oracle PL/SQL >> RANK Bookmark and Share

<< lastnext >>

Snippet Name: RANK

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.

Also see:
» REGR_SLOPE
» VARIANCE
» VAR_SAMP
» VAR_POP
» SUM
» STDDEV_SAMP
» STDDEV_POP
» STDDEV
» ROW_NUMBER
» REGR_SYY
» REGR_SXY
» REGR_SXX
» REGR_R2
» REGR_INTERCEPT
» REGR_COUNT
» REGR_AVGY
» REGR_AVGX
» Number Functions: RATIO_TO_REPORT
» Number Functions: RANK
» PERCENTILE_DISC
» PERCENTILE_CONT
» PERCENT_RANK
» OVER PARTITION BY
» NTILE
» MIN
» MAX
» LEAD
» LAST_VALUE
» LAST
» LAG

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009

RANK(<rank expression>) WITHIN GROUP
(ORDER BY <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 
     ( ORDER BY 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.
 
     SELECT RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
     FROM employees;
 
-- The syntax for the rank function when used as an Analytic 
-- function is:
 
    RANK() OVER ( [ query_partition_clause] ORDER BY clause )
 
     SELECT employee_name, salary,
     RANK() OVER (PARTITION BY department ORDER BY salary)
     FROM employees
     WHERE department = 'Marketing';


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


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.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 183 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?