Posted By

bbrumm on 02/06/17

Tagged

sql
Oracle

Versions (?)

Last Edited at 02/06/17 05:33am

Statistics

Viewed 701 times

Favorited by 0 user(s)

Related snippets

Oracle RANK Function

/ Published in: SQL

A few examples of the RANK function.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

--1 Basic
SELECT 
RANK(200) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
--2 Different value, duplicate
SELECT 
RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
--3 Different value, duplicate, but second column
SELECT 
RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
FROM student;
 
--4 Nulls first
SELECT 
RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
FROM student;
 
--5 Name but order by value - error
SELECT 
RANK('Tom') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
--6 Name sorting
SELECT 
RANK('Steven') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;
 
 
--7 Name that does not exist
SELECT 
RANK('Brad') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;
 
--Analytical Functions
 
--8 Basic - partition by gender
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
FROM student;
 
 
--9 Basic - partition by fees paid
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
FROM student;
 
--10 No partition
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (ORDER BY fees_paid) AS rank_val
FROM student;