Oracle RANK Function


/ Published in: SQL
Save to your folder(s)

A few examples of the RANK function.


Copy this code and paste it in your HTML
  1. --1 Basic
  2. SELECT
  3. RANK(200) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
  4. FROM student;
  5.  
  6. --2 Different value, duplicate
  7. SELECT
  8. RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
  9. FROM student;
  10.  
  11. --3 Different value, duplicate, but second column
  12. SELECT
  13. RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
  14. FROM student;
  15.  
  16. --4 Nulls first
  17. SELECT
  18. RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
  19. FROM student;
  20.  
  21. --5 Name but order by value - error
  22. SELECT
  23. RANK('Tom') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
  24. FROM student;
  25.  
  26. --6 Name sorting
  27. SELECT
  28. RANK('Steven') WITHIN GROUP (ORDER BY first_name) AS rank_val
  29. FROM student;
  30.  
  31.  
  32. --7 Name that does not exist
  33. SELECT
  34. RANK('Brad') WITHIN GROUP (ORDER BY first_name) AS rank_val
  35. FROM student;
  36.  
  37. --Analytical Functions
  38.  
  39. --8 Basic - partition by gender
  40. SELECT
  41. student_id, first_name, last_name, gender, fees_paid,
  42. RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
  43. FROM student;
  44.  
  45.  
  46. --9 Basic - partition by fees paid
  47. SELECT
  48. student_id, first_name, last_name, gender, fees_paid,
  49. RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
  50. FROM student;
  51.  
  52. --10 No partition
  53. SELECT
  54. student_id, first_name, last_name, gender, fees_paid,
  55. RANK() OVER (ORDER BY fees_paid) AS rank_val
  56. FROM student;

URL: http://www.databasestar.com/oracle-rank/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.