ORDER BY

In the Ultra Transcenders books

DP-900

Each book explains ORDER BY in context, with comparison tables and the common traps.

Terms in this definition

• Serverless

  Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.

• SEQUENCE

  A schema-bound object that generates values on request via NEXT VALUE FOR without belonging to a table. One can feed several tables, restart, cycle or set aside a block of values; any value drawn inside a transaction that rolls back is gone.

Related terms

• DAX query

  A query against a semantic model that returns a table, comparable to a T-SQL SELECT. It is written with EVALUATE, optionally preceded by DEFINE for query-only measures and variables and followed by ORDER BY, and it doesn't alter the model.

• EVALUATE

  Keyword that every DAX query must contain; whatever table expression follows it becomes the query's output. Clauses such as DEFINE, ORDER BY and START AT may be added if needed.

• Exact nearest neighbour

  Measures how far the query vector is from each and every candidate, for instance using VECTOR_DISTANCE with TOP and ORDER BY, so the results are always right. It is advised for up to roughly 50,000 candidates; beyond that, ANN (approximate) search is preferred.

• JSON_ARRAYAGG

  An aggregate function that combines each group's values into a JSON array, which can optionally be sorted with ORDER BY.

• OVER

  Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.

• Ranking functions

  The T-SQL window functions ROW_NUMBER, RANK, DENSE_RANK and NTILE, which assign numbers to rows in each partition according to the ORDER BY in their OVER clause.

• RRF

  A Cosmos DB system function, used with ORDER BY RANK, that merges the rankings from VectorDistance and FullTextScore, with optional weights, to give hybrid search results.

• Serialized row set

  A KQL row set with a guaranteed order, created by the serialize, sort or top operators and kept in order by operators like where, project and extend. Window functions including row_number(), row_cumsum(), prev() and next() can only run on such a row set.