What you will be able to do
- Write ORDER BY clauses that use ASC/DESC and NULLS FIRST/LAST, and predict where NULLs land when neither is specified
- Sort on several keys, with ORDER BY ALL and column positions, and recognise the errors that invalid sort keys raise
- Explain why ORDER BY gives a total order and SORT BY may not
- Use LIMIT, OFFSET and LIMIT ALL together with ORDER BY to get repeatable top-N results
Key concept
Total order (ORDER BY) — ORDER BY is the clause that guarantees the whole result set comes back in the order you asked for. LIMIT returns repeatable rows only when it follows such an ordering. SORT BY only orders rows inside each Spark partition.
1.ORDER BY: direction and NULL placement
A query with no ordering clause returns rows in whatever order the engine produces them. In Databricks SQL, the ORDER BY clause returns the result rows sorted in the order you specify. Each sort expression can take two optional modifiers. The sort direction is ASC or DESC, and it defaults to ascending when you leave it out. The NULL ordering is NULLS FIRST or NULLS LAST. The NULL default trips people up, so commit to an answer before reading on.
So NULLs don't have a fixed position. Their default follows the direction: first for ascending, last for descending. That means ORDER BY age and ORDER BY age DESC put the NULLs at opposite ends of the result. To override the default, add an explicit modifier. NULLS FIRST and NULLS LAST take effect whichever direction you sort in. That's how you get an ascending sort with the missing values at the bottom, or a descending sort with them at the top.
| What you write | Direction | Where NULLs go |
|---|---|---|
| ORDER BY age | Ascending (default) | First (default for ASC) |
| ORDER BY age DESC | Descending | Last (default for DESC) |
| ORDER BY age NULLS LAST | Ascending | Last |
| ORDER BY age DESC NULLS FIRST | Descending | First |
-- Sort rows by age in descending manner, which defaults to NULL LAST.
> SELECT name, age FROM person ORDER BY age DESC;Checkpoint 1 of 6· Fill the gap
You want ages in ascending order with the people who have no recorded age at the bottom. Which token completes the query?
> SELECT name, age FROM person ORDER BY age NULLS ? ;Ascending order puts NULLs first by default, so you need an explicit NULLS LAST. It moves the NULLs to the end whichever direction you sort in.
Source: docs.databricks.comCheckpoint 2 of 6· Exam question
An analyst runs `SELECT region, revenue FROM sales ORDER BY revenue;` against a table where some rows have a `NULL` revenue value. In the returned result set, where do those `NULL` revenue rows appear relative to the numeric values?
Correct answer: B — At the top of the result set, because ascending is the default sort direction and NULLs sort first under ascending ORDER BY unless NULLS LAST is stated
- A. Incorrect: that placement describes the default for descending order, not ascending order. With ascending sorting, NULLs move to the front of the result, not the back.
- B. Correct: when no direction is given, ORDER BY sorts ascending by default, and under ascending order Databricks SQL places NULL values first unless the query explicitly adds NULLS LAST to override that default.
- C. Incorrect: ORDER BY always repositions every row in the output according to the sort key, including NULLs, so rows do not retain arbitrary storage order once a sort is applied.
- D. Incorrect: ORDER BY runs successfully on numeric columns containing NULL values with no wrapper function required; NULL is a valid, well-defined sort value in Databricks SQL.
Sources1
2.Several sort keys, ORDER BY ALL and column positions
A single key rarely settles the order, because several rows can share the same value. You can list more than one expression, and sorting works left to right. All rows are sorted by the first expression. Rows with the same value for that expression are then ordered by the second expression, and so on. Each expression takes its own direction, so ORDER BY name ASC, age DESC is valid. If rows still match on every key, their relative order isn't deterministic. Add a tie-breaker, such as a unique id, whenever repeatable output matters.
There are two shorthands. ORDER BY ALL (Databricks SQL, and Databricks Runtime 12.2 LTS and above) sorts by every expression in the SELECT list, in the order they appear. A direction or NULLS modifier written after ALL applies to each of those expressions. You can also write an integer literal, such as ORDER BY 1, which is read as a position in the select list, not as a constant. A position that doesn't exist raises ORDER_BY_POS_OUT_OF_RANGE. Sorting by a type that can't be ordered, such as MAP, raises DATATYPE_MISMATCH.INVALID_ORDERING_TYPE.
-- Sort rows based on all columns in the select list
> SELECT * FROM person ORDER BY ALL ASC;Checkpoint 3 of 6· Check yourself
The person table has columns id, name and age. What happens when you run SELECT name FROM person ORDER BY 2;?
An integer literal in ORDER BY is a position in the select list, not in the table. This select list has only one column, so position 2 is out of range.
“If the expression is a literal INTEGER value it is interpreted as a column position in the select list.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A dashboard designer wants the newest orders to always appear first, and among orders placed on the same day, wants missing `ship_date` values grouped with the earliest-shipped orders rather than trailing at the end. Which clause satisfies both requirements?
Correct answer: D — `ORDER BY order_date DESC, ship_date ASC NULLS FIRST`, because it sorts newest orders first and explicitly forces NULL ship dates to the front of each same-day group
- A. Incorrect: descending order defaults NULLs to the end of the sort, not the front, so this arrangement would push missing ship dates to the bottom of each group instead of the top.
- B. Incorrect: while ascending order does default NULLs to the front, relying on the implicit default without stating NULLS FIRST leaves the intent unstated; the requirement is better met by declaring it explicitly, as the correct clause does.
- C. Incorrect: NULLS FIRST and NULLS LAST can be attached to any sort key regardless of whether earlier keys use ascending or descending order, so there is no dependency requiring order_date to be ascending first.
- D. Correct: descending order on order_date surfaces the newest orders first, and the explicit NULLS FIRST override on the second sort key guarantees missing ship dates land at the start of each order_date group regardless of the ascending default.
Sources1
3.SORT BY is not ORDER BY
SORT BY looks almost the same as ORDER BY. It accepts expressions, column positions, a direction and NULL ordering, with the same defaults: ascending, NULLs first for ASC and last for DESC. The difference is scope. SORT BY returns rows sorted within each Spark partition. When the data is spread across several partitions, the overall result can be only partially ordered. To control how rows are split into partitions, use the REPARTITION hint, as in the documented example below. ORDER BY gives a fully ordered result however Spark splits the data.
-- Sort rows by `name` within each partition in ascending manner
> SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person
SORT BY name;Each zip_code partition is sorted on its own: first the 94588 group, then the 94511 group. Nothing sorts the result as a whole, so the order is only correct within each partition.
Checkpoint 5 of 6· Check yourself
A dashboard needs a customer list in strict alphabetical order across the whole result. Which clause guarantees it?
Only ORDER BY guarantees a fully ordered result. SORT BY orders rows within each partition, and the REPARTITION hint only controls how the rows are split into partitions.
“This is different than the ORDER BY clause which guarantees a fully ordered output regardless of how Spark splits the data.”Source: docs.databricks.com
Sources2
4.LIMIT and OFFSET: returning a slice of the sorted result
After sorting, you often want only the first few rows. LIMIT caps the number of rows the query returns. It is generally used together with ORDER BY so that the results are deterministic. Without an ordering, 'the first two rows' has no fixed meaning. LIMIT ALL applies no limit at all. Adding OFFSET skips rows before the limit applies. In the documented example, LIMIT 2 OFFSET 3 returns the 4th and 5th rows in alphabetical order.
-- Select the 4th and 5th rows by alphabetical order.
> SELECT name, age FROM person ORDER BY name LIMIT 2 OFFSET 3;The LIMIT value doesn't have to be a bare number. It must be a literal expression that returns an integer, so a function over constants is fine. An expression that isn't foldable, isn't an integer, evaluates to NULL or is negative raises INVALID_LIMIT_LIKE_EXPRESSION. In practice, a column reference or a negative number in LIMIT is an error, not an empty result.
Checkpoint 6 of 6· Check yourself
An analyst runs SELECT name, age FROM person LIMIT 2 to get the two oldest people, and sees different rows from one run to the next. What is the fix?
LIMIT only caps the row count. Without ORDER BY nothing decides which rows count as 'first', so the result isn't guaranteed to be the top N or to stay the same between runs.
“In general, this clause is used in conjunction with ORDER BY to ensure that the results are deterministic.”Source: docs.databricks.com
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.NULLs always sort to the end of an ORDER BY result.Why is that wrong?
The default follows the direction: NULLs come first for ascending (the default) and last for descending. Use NULLS FIRST or NULLS LAST to override it.
Covered in ORDER BY: direction and NULL placement
2.ORDER BY 2 sorts by the table's second column.Why is that wrong?
An integer literal refers to a position in the SELECT list. If that position doesn't exist, the query fails with ORDER_BY_POS_OUT_OF_RANGE.
Covered in Several sort keys, ORDER BY ALL and column positions
3.SORT BY and ORDER BY are interchangeable because they take the same options.Why is that wrong?
SORT BY orders rows only within each Spark partition and may return a partially ordered result. ORDER BY guarantees a total order.
Covered in SORT BY is not ORDER BY
4.LIMIT N on its own returns the top N rows.Why is that wrong?
LIMIT only caps the row count. You need it together with ORDER BY to get a deterministic, meaningful 'top N'.
Covered in LIMIT and OFFSET: returning a slice of the sorted result
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-orderbyOfficial docs
“NULLS FIRST: NULL values are returned first regardless of the sort order.”
↩︎ ORDER BY: direction and NULL placement“If sort direction is not explicitly specified, then by default rows are sorted ascending.”
↩︎ ORDER BY: direction and NULL placement“The resulting order not deterministic if there are duplicate values across all order by expressions.”
↩︎ Several sort keys, ORDER BY ALL and column positions“A shorthand equivalent to specifying all expressions in the SELECT list in the order they occur.”
↩︎ Several sort keys, ORDER BY ALL and column positions“Unlike the SORT BY clause, this clause guarantees a total order in the output.”
↩︎ Key concept“NULLs sort first if sort order is ASC and NULLS sort last if sort order is DESC”
↩︎ Exam trap 1“If the expression is a literal INTEGER value it is interpreted as a column position in the select list.”
↩︎ Exam trap 2“NULLs sort first if sort order is ASC and NULLS sort last if sort order is DESC”
↩︎ Prediction“If the expression is a literal INTEGER value it is interpreted as a column position in the select list.”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-sortbyOfficial docs
“Returns the result rows sorted within each Spark partition in the user specified order.”
↩︎ SORT BY is not ORDER BY“When the data is spread across multiple Spark partitions, SORT BY might return a partially ordered result.”
↩︎ Exam trap 3“When the data is spread across multiple Spark partitions, SORT BY might return a partially ordered result.”
↩︎ Prediction“This is different than the ORDER BY clause which guarantees a fully ordered output regardless of how Spark splits the data.”
↩︎ Checkpoint - 3.
“If specified, the query returns all the rows. In other words, no limit is applied if this option is specified.”
↩︎ LIMIT and OFFSET: returning a slice of the sorted result“In general, this clause is used in conjunction with ORDER BY to ensure that the results are deterministic.”
↩︎ Exam trap 4“If the expression is not foldable, is not of integer type, evaluates to NULL, or evaluates to a negative value”
↩︎ Prediction“In general, this clause is used in conjunction with ORDER BY to ensure that the results are deterministic.”
↩︎ Checkpoint