Top-N per Group

Apache Spark · Window Functions

Theory

Top-N per group means keeping the best N rows inside each group, such as the two biggest orders in every region or the latest three logins per user. In PySpark the standard pattern is: build a window partitioned by the group and ordered by the ranking column, add a rank column with row_number(), rank() or dense_rank(), filter where the rank is at most N, then drop the helper column.

The function choice decides ties. row_number returns exactly N rows per group. rank and dense_rank may return more than N when values tie, which is correct for questions like 'all products with the highest sales'. For N = 1 an alternative is groupBy with F.max_by("product", "amount"), or a max of a struct, which avoids sorting whole partitions. Spark can also push the limit into the window step as a WindowGroupLimit, so filtering on rank <= N is efficient.

Why we use it

Top-N per group appears in almost every analytics job and interview: best sellers per category, highest-paid employees per department, latest record per key. A plain orderBy + limit only gives the global top N, not per group.

When to use it

Use the window + filter pattern for any N per group. Use row_number when you need exactly N rows, dense_rank for 'top N distinct values'. For only the single top row and a couple of columns, groupBy with max_by is simpler and cheaper.

How it works

  1. DataFrame with a group column and a score column
  2. Window.partitionBy(group).orderBy(desc(score))
  3. Should ties return extra rows? Yes: Use rank or dense_rank. No: Use row_number.
  4. Filter rank column <= N
  5. Drop the helper column
  6. Top N rows for every group

Syntax

w = Window.partitionBy("region").orderBy(F.desc("amount"))
(df.withColumn("rn", F.row_number().over(w))
   .filter(F.col("rn") <= 2)
   .drop("rn"))
# single top row: df.groupBy("region").agg(F.max_by("product", "amount"))

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, 101, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (2, 102, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (3, 101, "North", "Mouse",  3,  1500.0, "2026-01-07"),
    (4, 103, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (5, 102, "West",  "Tablet", 1, 25000.0, "2026-01-09"),
    (6, 104, "North", "Phone",  1, 18000.0, "2026-01-10"),
    (7, 103, "South", "Mouse",  2,  1000.0, "2026-01-11"),
    (8, 105, "West",  "Laptop", 1, 60000.0, "2026-01-12"),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

customers = spark.createDataFrame([
    (101, "Asha",  "Jaipur", "Retail"),
    (102, "Ravi",  "Pune",   "Corporate"),
    (103, "Meera", "Delhi",  "Retail"),
    (104, "Karan", "Jaipur", "SMB"),
    (105, "Priya", "Mumbai", "Corporate"),
], "customer_id INT, name STRING, city STRING, segment STRING")

w = Window.partitionBy("region").orderBy(F.desc("amount"), "order_id")

top2 = (orders
    .withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") <= 2)
    .join(customers, "customer_id")
    .select("region", "rn", "order_id", "name", "product", "amount")
    .orderBy("region", "rn"))
top2.show()

# N = 1 shortcut without a window
orders.groupBy("region").agg(
    F.max("amount").alias("max_amount"),
    F.max_by("product", "amount").alias("top_product")).orderBy("region").show()

# Output:
# +------+---+--------+-----+-------+-------+
# |region| rn|order_id| name|product| amount|
# +------+---+--------+-----+-------+-------+
# | North|  1|       1| Asha| Laptop|55000.0|
# | North|  2|       6|Karan|  Phone|18000.0|
# | South|  1|       4|Meera| Laptop|52000.0|
# | South|  2|       7|Meera|  Mouse| 1000.0|
# |  West|  1|       8|Priya| Laptop|60000.0|
# |  West|  2|       2| Ravi|  Phone|30000.0|
# +------+---+--------+-----+-------+-------+
#
# +------+----------+-----------+
# |region|max_amount|top_product|
# +------+----------+-----------+
# | North|   55000.0|     Laptop|
# | South|   52000.0|     Laptop|
# |  West|   60000.0|     Laptop|
# +------+----------+-----------+

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