Oracle ROWNUM in Snowflake, Databricks and BigQuery
None of the targets has ROWNUM. Use LIMIT for "first n rows" filters and ROW_NUMBER() OVER (...) when the number itself is needed.
Oracle syntax: ROWNUM · Oracle docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | LIMIT n / ROW_NUMBER() OVER (ORDER BY ...) | QUALIFY ROW_NUMBER() OVER (...) <= n filters on the window result without a subquery. | Snowflake docs |
| Databricks | Rewrite | LIMIT n / row_number() OVER (ORDER BY ...) | QUALIFY is supported as well. | Databricks docs |
| BigQuery | Rewrite | LIMIT n / ROW_NUMBER() OVER (ORDER BY ...) | QUALIFY is supported as well. | BigQuery docs |
Behavior differences to check
- Oracle assigns ROWNUM before ORDER BY, so WHERE ROWNUM <= 10 ... ORDER BY x returns 10 arbitrary rows sorted afterwards. Decide whether the old query meant that or "top 10 by x" before translating it to LIMIT.
Examples
Top 10 customers by revenue
SELECT * FROM (SELECT customer_id, revenue FROM customers ORDER BY revenue DESC) WHERE ROWNUM <= 10;SELECT customer_id, revenue FROM customers ORDER BY revenue DESC LIMIT 10;SELECT customer_id, revenue FROM customers ORDER BY revenue DESC LIMIT 10;SELECT customer_id, revenue FROM customers ORDER BY revenue DESC LIMIT 10;