ORDERBY

In the Ultra Transcenders books

DP-600PL-300

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

Terms in this definition

• Azure AI Search partition

  Storage and I/O unit of a search service, each holding part of the indexes. Adding partitions increases storage and indexing throughput; the number of search units equals replicas multiplied by partitions.

• DAX

  Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.

• Window functions

  Calculate across rows related to whichever row is current, e.g. rankings or running totals. DAX provides OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, partitioned and ordered through PARTITIONBY and ORDERBY; T-SQL uses OVER, applied once GROUP BY and HAVING finish.

• LIKE

  Compares strings with a pattern that can contain the % and _ wildcards. Because it only understands character patterns, searching big volumes of text this way is much slower than using full-text search.

• OFFSET

  A DAX window function that moves a given number of rows back (negative values) or forward (positive) from the current row in a sorted table, optionally split into partitions. It is commonly used to fetch the prior or next period's value.

• RANK

  A window function, found in T-SQL (used with OVER) and in DAX, that gives each row's position within its partition. In DAX it accepts ORDERBY and PARTITIONBY, skips ranks after ties unless DENSE is chosen, and returns blank for total rows.

• ROWNUMBER

  A DAX window function that gives the current row a unique position within a sorted, optionally partitioned table. Where the sort and partition columns leave a tie, it brings in more columns to settle it, and errors if there are none; RANK, by contrast, lets tied rows share a position.