Notes on SQL 5

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2


• Notes on SQL 3


• Notes on SQL 4

Common Table Expressions (CTEs)

Basic CTE:

WITH TableName As (<Any SELECT statement that generates a valid table>)
<SELECT statement that references TableName>

Recursive CTE:

WITH TableName As (<Any SELECT statement that produces the anchor result> UNION ALL <SELECT statement that references TableName>)
<SELECT statement that references TableName>

Use OPTION(MAXRECURSION = <number>) in the outer query to determine the max amount of recursive calls.

More details on CTEs here.

Note: since the WITH keyword has several possible meanings in different contexts, the statements preceding the WITH keyword are required to be terminated with a semicolon.

Ranking data functions


ROW_NUMBER: specifies the row number for each row in the resultset. Could be use to paginate in SPs (WHERE RowNumber BETWEEN @min AND @max). Syntax: ROW_NUMBER() OVER ([ <partition_by_clause> ] <order_by_clause>). The partition separates the result set into subsets to which the ROW_NUMBER() function applies independently (row numbering is restarted for each subset). The order by specifies the order in which to number rows. DOES NOT WORK WELL WITH TIES (of the order_by_clause), since it could return different row numberings when executing the same query.

RANK: same as ROW_NUMBER, but if there are ties the same value is assigned to all rows in a tie, and skips the corresponding amount of values for the next assignment. E.g. 1, 2, 3, 3, 5, 5, 5, 8, 9, etc.

DENSE_RANK: same as RANK but does not skip values. E.g. 1, 2, 3, 3, 4, 4, 4, 5, 6, etc.

NTILE(integer_expression): partitions the result set into  a specified number of groups, and rows in each group are assigned a number that indicates to which group they pertain. (Note: "If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause").