SQL functions
Practical SQL function reference pages for the most-used patterns.
Common building blocks
ROW_NUMBER
Assign a unique row number within each partition.
RANK
Rank rows and keep ties together.
DENSE_RANK
Rank rows with ties together and no gaps in the sequence.
COUNT
Count rows or non-NULL values in a group.
SUM
Aggregate values across a group.
AVG
Average a set of values, with NULL and integer-division caveats.
MIN / MAX
Return the smallest or largest value in a group.
COALESCE
Return the first non-NULL value.
Why these matter
Most business logic fails not because of a complex algorithm, but because the team did not agree on:
- what counts as a row
- how
NULLvalues are treated - how ties are ordered
- whether a window function or aggregate is the right tool
Good habits
- prefer explicit
ORDER BYin window functions - use
PARTITION BYfor per-group ranking - guard nullable results with
COALESCEorCASE - document tie policy when ranking and paging
- add a stable tiebreaker before shipping user-facing ranking logic