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
- DataFrame with a group column and a score column
- Window.partitionBy(group).orderBy(desc(score))
- Should ties return extra rows? Yes: Use rank or dense_rank. No: Use row_number.
- Filter rank column <= N
- Drop the helper column
- 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
- Pattern: window ordered desc -> row_number/rank -> filter rn <= N -> drop rn
- orderBy + limit gives the global top N, not top N per group
- row_number gives exactly N rows; rank/dense_rank can return more on ties
- Add a tie-breaker column so results are repeatable
- For N = 1, groupBy + max_by avoids sorting every row