RANK(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>)
 
conn oe/oe
 
SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees
WHERE department_id = 80;
 
/* The following query finds the 5 top-selling products for 
each product subcategory where that product contributes more 
than 20% of the sales within its product category. */
 
conn sh/sh
 
col categ format a15
col prod_subcategory format a20
 
SELECT SUBSTR(prod_category,1,8) AS CATEG, prod_subcategory, prod_id, sales
FROM (
  SELECT p.prod_category, p.prod_subcategory, p.prod_id,
  SUM(amount_sold) AS SALES, SUM(SUM(amount_sold))
  OVER (PARTITION BY p.prod_category) AS CAT_SALES,
  SUM(SUM(amount_sold))
  OVER (PARTITION BY p.prod_subcategory) AS SUBCAT_SALES,
  RANK() OVER (PARTITION BY p.prod_subcategory
  ORDER BY SUM(amount_sold) ) AS RANK_IN_LINE
  FROM sales s, customers c, countries co, products p
  WHERE s.cust_id = c.cust_id
  AND c.country_id = co.country_id
  AND s.prod_id = p.prod_id
  AND s.time_id = TO_DATE('11-OCT-2000')
  GROUP BY p.prod_category, p.prod_subcategory, p.prod_id
  ORDER BY prod_category, prod_subcategory)
WHERE SUBCAT_SALES > 0.2 * CAT_SALES
AND RANK_IN_LINE<=5;