/*----------------------------------------------------------------------------*/
/* Using rank() over(...) function
/*       within the over(...) clause, you must order by, 
/*       within the over(...) clause, you can optionally partition (group by) 
/* rank()       will skip numbers if ranking tied
/* dense_rank() will will not skip numbers if ranking tied 
/*----------------------------------------------------------------------------*/

SELECT course.*, RANK( ) OVER(ORDER BY price) as rank
FROM course; 



SELECT RANK()       OVER(order by price desc) as rank,
       DENSE_RANK() OVER(order by price desc) as dense_rank,
       course.* 
FROM course; 



SELECT RANK()       OVER(order by amount desc) as rank,
       DENSE_RANK() OVER(order by amount desc) as dense_rank,
       RANK()       OVER(partition by vendor order by amount desc) as "Partitioned by Vendor",
       vendor, description, amount 
FROM payment 
ORDER BY 1, 2, 3;



SELECT vendor, avg(amount),
       RANK()       OVER(order by avg(amount) desc) as rank,
       DENSE_RANK() OVER(order by avg(amount) desc) as dense_rank
FROM payment 
GROUP by vendor 
ORDER BY rank, dense_rank;



SELECT RANK()       OVER(order by avg_paid desc) as rank,
       DENSE_RANK() OVER(order by avg_paid desc) as dense_rank,
       vendor, avg_paid
FROM 
    (SELECT vendor, AVG(amount) avg_paid
     FROM payment
     GROUP by vendor) subquery
ORDER BY rank, dense_rank;



SELECT DENSE_RANK() OVER(order by courses desc) as dense_rank, lname, fname, courses
from
(
    SELECT lname, fname, COUNT(course_id) courses
    FROM   student JOIN class ON ssn=stu_ssn
    GROUP BY lname, fname
) temp
ORDER BY 1, 2, 3;