Runway to SQL Window functions

ROW_Number()

It numbers the output of a result set.

ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression ] order_by_clause )

SELECT *, ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column2 ASC or DESC) rank
FROM table

PARTITION BY: Divides the result set produced by the FROM clause into partitions. We can define which rows the window function would be applied to. It would be applied to all rows if PARTITION BY is omitted.

Note: There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

1. Values of the PARTITION column are unique.
2. Values of the ORDER BY columns are unique.
3. Combinations of values of the PARTITION column and ORDER BY columns are unique.

RANK()

Returns the rank of each row within the partition of a result set. RANK provides the same numeric value for ties.

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

SELECT *, RANK() OVER(ORDER BY column1 DESC or ASC) AS rank
FROM table

DENSE_RANK()

This function returns the rank of each row within a result set partition, with no gaps in the ranking values.

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

SELECT DENSE_RANK() OVER(ORDER BY column1 DESC or ASC) AS rank
FROM table

NTILE()

Distributes the rows in an ordered partition into a specified number of groups. We need to specify the value for the desired number of groups.

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

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.

LAG()

Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a given physical offset that comes before the current row.

LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )

Note: LAG is nondeterministic.

LEAD()

Accesses data from a subsequent row in the same result set without the use of a self-join. LEAD provides access to a row at a given physical offset that follows the current row.

LEAD ( scalar_expression [ ,offset ] , [ default ] )
OVER ( [ partition_by_clause ] order_by_clause )

Note: LEAD is nondeterministic.

Subscribe to my space here: https://qr.ae/pyRsLp