Oracle analytics functions: RANK and row_number for reports

The need to rank

Sales reports often require ratings, for example three best-selling products per category. Oracle's analytic function calculates this directly in a single query without repeated joins.

SELECT
    kategori,
    nama_produk,
    total_terjual,
    RANK() OVER (
        PARTITION BY kategori
        ORDER BY total_terjual DESC
    ) AS peringkat
FROM ringkasan_penjualan;

Difference between RANK, density_rank, and row_number

RANK skip the next number if there is the same value, DENSE_RANK does not jump, whereas ROW_NUMBER always give consecutive numbers even if the values are the same.

SELECT
    nama_produk,
    total_terjual,
    RANK() OVER (ORDER BY total_terjual DESC) AS rank_biasa,
    DENSE_RANK() OVER (ORDER BY total_terjual DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY total_terjual DESC) AS nomor_baris
FROM ringkasan_penjualan;

Fetching top N per group

Wrap the analytic query as a subquery, then filter the results in the outer query because the analytic function cannot be directly used in the clause WHERE.

SELECT * FROM (
    SELECT
        kategori,
        nama_produk,
        RANK() OVER (PARTITION BY kategori ORDER BY total_terjual DESC) AS peringkat
    FROM ringkasan_penjualan
)
WHERE peringkat <= 3;

PARTITION BY groups calculations

Piped PARTITION BY, the ranking is calculated for the entire table. With PARTITION BY kategori, ratings are recalculated starting from 1 in each category.

practice;

Change the query above to show five best-selling products overall, without grouping by category.