MariaDB Tutorial / Compare ROW_NUMBER, RANK, and DENSE_RANK

Window functions add calculations over an ordered set while retaining the input result rows. ROW_NUMBER assigns a different number to every row, whereas RANK and DENSE_RANK can assign equal ranks to equal values; choose the intended tie policy before selecting a function.

Separate row numbering from shared ranking

Equal customer purchase totals can require distinct display row numbers but a shared purchase rank. First aggregate one total per customer, then rank those customer rows. Changing a function name alone cannot fix a different input grain or tie definition. Use Break Queries into Named Steps with WITH and CTEs to separate aggregation from ranking; compare Calculate Running Totals and Compare Rows with LAG and LEAD when the same row order drives accumulation and previous-value calculations.

Check the MariaDB documentation for the syntax and conditions used here: row_number, window functions overview.

Prepare customers with deliberately equal totals

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c32_customers, c32_products, c32_orders, c32_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c32_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c32_order_customer FOREIGN KEY (customer_id)
  REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c32_item_order FOREIGN KEY (order_id)
  REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
  REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c32_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Customers 1 and 2 each have paid goods worth 19500.00. Customer 3 has only a canceled order and customer 4 none; this report explicitly assigns both a paid total of zero. Including nonbuyers and excluding canceled amounts are part of the report definition.

Aggregate customer totals and compare the three functions

WITH customer_totals AS (
 SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid'
  THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
 FROM c32_customers c LEFT JOIN c32_orders o
  ON o.customer_id=c.customer_id
 LEFT JOIN c32_items i ON i.order_id=o.order_id
 GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
 ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
 RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
 DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;

ROW_NUMBER includes customer_id as a unique tie-breaker. The two rank functions order only by amount, preserving equal ranks for equal totals. Adding the unique customer ID to those ranking definitions would make the ordering combinations different and eliminate the intended ties.

Distinguish window order, final order and partitions

ORDER BY inside OVER defines the function’s calculation order. The final SELECT’s ORDER BY defines output display order. State both when both matter; a window ordering alone does not promise the final row sequence.

PARTITION BY defines groups within which window calculations restart. A regional ranking would need the city in the aggregated input and an appropriate PARTITION BY city. Unlike GROUP BY, partitioning does not itself collapse customer rows.

This complete query restarts the row number within each city while retaining one row per customer. It uses the existing c32 fixture and orders the displayed result by customer ID.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
       ROW_NUMBER() OVER (PARTITION BY city
         ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;

Customers 1 and 3 share the Seoul partition and receive 1 and 2. Customers 2 and 4 each start a different partition at 1. Remove only PARTITION BY city to compare one global sequence, then restore the city partition.

Filter a ranked result in an outer query

The same SELECT’s WHERE cannot directly filter its newly created row_no. Wrap the ranked result in a CTE or derived table, then filter the outer query, for example with row_no <= 2. “Two rows” and “everyone tied for first” are different requests, so choose which calculated field to filter.

Ranking raw item details instead would rank item rows rather than customers. Keep the customer aggregation and the window calculation as separate logical stages so the report’s unit remains visible.

WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
       COALESCE(SUM(CASE WHEN o.status='paid'
         THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
 SELECT customer_id,customer_name,goods_total,
        ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
        RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
 FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;

The result contains customers 1 and 2 with row numbers 1 and 2 and rank 1 for both. Change only the outer condition to row_no <= 1 for one row, or to amount_rank = 1 for both first-place ties. These are different requirements; restore row_no <= 2 after comparing them.

Verify ties and the qualifying population

Use deliberate ties and compare what happens to the following rank. Reverse only the customer-ID tie-breaker to observe the ordering within ROW_NUMBER ties without changing amount-based ranks. Confirm the canceled-order and nonbuyer policy. A generated rank is a report calculation, not a replacement for a stable customer identifier.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;