row_number, rank & dense_rank

Apache Spark · Window Functions

Theory

These three ranking functions number the rows inside each window partition in the order given by orderBy. row_number() always gives 1, 2, 3, ... with no repeats, even when two rows tie, so the tie is broken arbitrarily unless you add a tie-breaker column. rank() gives tied rows the same number and then skips, so two orders tied for first are both 1 and the next is 3. dense_rank() also gives ties the same number but does not skip, so the next one is 2.

For example, ranking orders by amount within West where two Phones both cost 30000: row_number gives 1 and 2, rank gives 1 and 1 then 3, dense_rank gives 1 and 1 then 2. Ranking functions need an ordered window; Spark raises an error if the window has no orderBy. Related functions are percent_rank(), ntile(n) and cume_dist().

Why we use it

Ranking is the basis of top-N reports, leaderboards, picking the latest record per key and removing duplicates. Choosing the right function decides how ties behave, which is a very common source of wrong numbers and a favourite interview question.

When to use it

Use row_number when you need exactly one row per position (dedup, latest record). Use rank for competition style ranking where ties share a place and gaps follow. Use dense_rank for 'Nth highest value' questions where ties should not create gaps.

How it works

  1. Rows inside one partition, sorted by orderBy
  2. Walk the rows in order
  3. Is this row tied with the previous one? Yes: rank and dense_rank repeat the number; row_number still adds 1. No: All three move to the next number.
  4. rank jumps by the number of tied rows; dense_rank goes up by one
  5. Each row gets its position number

Syntax

w = Window.partitionBy("region").orderBy(F.desc("amount"))
F.row_number().over(w)
F.rank().over(w)
F.dense_rank().over(w)
F.ntile(4).over(w)   # quartiles

Example

from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

orders = spark.createDataFrame([
    (1, "West", "Phone",  30000.0), (2, "West", "Phone", 30000.0),
    (3, "West", "Tablet", 25000.0), (4, "West", "Laptop", 60000.0),
    (5, "North", "Laptop", 55000.0), (6, "North", "Mouse", 1500.0),
], "order_id INT, region STRING, product STRING, amount DOUBLE")

by_amount = Window.partitionBy("region").orderBy(F.desc("amount"))
by_amount_id = Window.partitionBy("region").orderBy(F.desc("amount"), "order_id")  # tie-breaker

ranked = (orders
    .withColumn("row_number", F.row_number().over(by_amount_id))
    .withColumn("rank", F.rank().over(by_amount))
    .withColumn("dense_rank", F.dense_rank().over(by_amount))
    .orderBy("region", "row_number"))

ranked.show()

# Output:
# +--------+------+-------+-------+----------+----+----------+
# |order_id|region|product| amount|row_number|rank|dense_rank|
# +--------+------+-------+-------+----------+----+----------+
# |       5| North| Laptop|55000.0|         1|   1|         1|
# |       6| North|  Mouse| 1500.0|         2|   2|         2|
# |       4|  West| Laptop|60000.0|         1|   1|         1|
# |       1|  West|  Phone|30000.0|         2|   2|         2|
# |       2|  West|  Phone|30000.0|         3|   2|         2|
# |       3|  West| Tablet|25000.0|         4|   4|         3|
# +--------+------+-------+-------+----------+----+----------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic