Beware of ROW_NUMBER without ORDER BY

UpdatedMarch 2, 2026

•1 min read

Beware of ROW_NUMBER without ORDER BY

Constantin Lungu

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified

Part of seriesBigQuery Window Functions

Haven’t posted all summer, but this bug pulled me straight out of the shadows.

I recently faced a mystery that pushed me to the edge of despair.

It seemed like a simple issue at first glance. A report kept changing completely at random.

I spent a good few days chasing it across multiple weeks . Imagine juggling several tables with time travel, all joined together. Trying to catch the bug.

I started to wonder if time travel even worked correctly. I wasn’t able to reproduce previous states, even when all the inputs had data from that exact point in time.

I began to question if I’d make it. The culprit?

Take this as a cautionary tale against using ROW_NUMBER() OVER(PARTITION BY …) without an accompanying ORDER BY.

Tucked into a table somewhere, it haunted me and wreaked havoc. I don’t know if there’s a real use case for it like that — but expect surprises.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Enjoyed this? Here are some related articles you might find useful:

Comments

Join the discussion

No comments yet. Be the first to comment.

BigQuery Window Functions

Part 2 of 13

A focused guide to window functions in BigQuery — cumulative sums, rolling averages, ranking functions, named windows, RANGE frames, and more.

Transforming cumulative sums into monthly values

Here’s a quick BigQuery SQL exercise. I often work with cumulative aggregations, but it’s not every day that I need to reverse them—converting cumulative values back into monthly figures. Let's look at an example. The dataset provides cumulative sale...

More from this blog

BigQuery Saves Your Query Results — Here's How to Find Them

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

Mar 5, 20261 min read

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

Feb 7, 20261 min read

Parameters in BigQuery

Flattening JSON arrays in BigQuery

I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN(). It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are. So where is this actually useful?➡️ Ha...

Dec 7, 20251 min read

Flattening JSON arrays in BigQuery

Using LAST_VALUE with STRUCTS

Even an “empty” STRUCT is still technically something. Not the same as a standalone NULL value. This is why, if you work with STRUCTs in SQL and try to find the latest non-empty struct using LAST_VALUE(...) IGNORE NULLS, you’ll notice it doesn’t help...

Oct 5, 20251 min read

Using LAST_VALUE with STRUCTS

Table grain quick validation with SQL

I was doing some exploratory data analysis on a number of tables I didn’t have much information about and, unfortunately, didn’t know their grain. I needed a quick way to validate my assumptions about the table grain, identify contradicting observati...

Oct 4, 20251 min read

Table grain quick validation with SQL

D

Datawise: A blog on SQL, BigQuery, Analytics, Python Tips and Tricks

204 posts

Data Engineer with a passion for transforming complex data landscapes into insightful stories. Here on my blog, I share insights, challenges, and the ever-evolving dance of technology and business.