Rank and deduplicate

Handle ties, choose one row per key, and return deterministic top results.

Step 1 of 6 · Learn

Handle ties

rank gives equal item counts the same rank and leaves gaps afterward. With counts 4, 3, 2, 2, 1, 1, the ranks are 1, 2, 3, 3, 5, 5. dense_rank removes the gaps. Do not add order_id to this ranking window: it would break the ties you want to preserve. The final display sort is separate.

Lesson reference: PySpark and Scala

Handle ties

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import col, rank

result = orders.withColumn(
    "item_rank", rank().over(Window.orderBy(col("item_count").desc()))
).select("order_id", "item_count", "item_rank").orderBy("order_id")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "item_rank", rank().over(Window.orderBy(col("item_count").desc()))
).select("order_id", "item_count", "item_rank").orderBy("order_id")
result.show(false)

Choose one row per key

row_number assigns a distinct position within each customer. Filtering position 1 keeps the lowest-total order here. This is a business selection rule, not removal of identical records. Unlike dropDuplicates on customer_id, the ordered window specifies which order survives. order_id breaks equal-total ties.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import col, row_number

result = orders.withColumn(
    "position", row_number().over(
        Window.partitionBy("customer_id").orderBy(col("total").asc(), col("order_id").asc())
    )
).filter(col("position") == 1).select("order_id", "customer_id", "total").orderBy("customer_id")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "position", row_number().over(
        Window.partitionBy("customer_id").orderBy(col("total").asc(), col("order_id").asc())
    )
).filter(col("position") === 1).select("order_id", "customer_id", "total").orderBy("customer_id")
result.show(false)

Keep a fixed number of rows

row_number with total descending and order_id ascending assigns a stable position to every order. Filtering the position keeps exactly the requested number when enough rows exist. Filtering rank instead can return extra rows at a tied cutoff. A global window is useful for this small example but sends all rows to one window partition.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import col, row_number

result = orders.withColumn(
    "position", row_number().over(Window.orderBy(col("total").desc(), col("order_id").asc()))
).filter(col("position") <= 2).select("order_id", "total", "position").orderBy("position")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "position", row_number().over(Window.orderBy(col("total").desc(), col("order_id").asc()))
).filter(col("position") <= 2).select("order_id", "total", "position").orderBy("position")
result.show(false)

example.pyPySpark

from pyspark.sql import Window from pyspark.sql.functions import col, rank result = orders.withColumn( "item_rank", rank().over(Window.orderBy(col("item_count").desc())) ).select("order_id", "item_count", "item_rank").orderBy("order_id") result.show(truncate=False)

Order rowsAssign ranks or positionsSelect output

4 itemsOrder IDs in this partition1004

3 itemsOrder IDs in this partition1002

2 itemsOrder IDs in this partition1001, 1006

1 itemsOrder IDs in this partition1003, 1005

Source orders6 rows

order_id	customer_id	status	total	item_count
1001	101	Delivered	89.5	2
1002	102	Shipped	149	3
1003	101	Cancelled	35	1
1004	103	Delivered	219.99	4
1005	104	Delivered	49.99	1
1006	105	Shipped	120	2

Handle ties

Result6 rows

order_id	item_count	item_rank
1001	2	3
1002	3	2
1003	1	5
1004	4	1
1005	1	5
1006	2	3

Orders 1001 and 1006 tie at rank 3. The next group starts at rank 5.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.