Advanced Search

Return to Snippet

Revision: 71284
at February 6, 2017 05:33 by bbrumm
Initial Code 
--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;
Initial URL 
http://www.databasestar.com/oracle-rank/
Initial Description 
A few examples of the RANK function.
Initial Title 
Oracle RANK Function
Initial Tags 
sql, Oracle
Initial Language 
SQL