Window functions in SQL Server

In SQL Server we have different categories of window functions

  • Aggregate functions – AVG, SUM, COUNT, MIN, MAX etc..
  • Ranking functions – RANK, DENSE_RANK, ROW_NUMBER etc..
  • Analytic functions – LEAD, LAG, FIRST_VALUE, LAST_VALUE etc…

OVER Clause defines the partitioning and ordering of a rows (i.e a window) for the above functions to operate on. Hence these functions are called window functions. The OVER clause accepts the following three arguments to define a window for these functions to operate on.

  • ORDER BY : Defines the logical order of the rows
  • PARTITION BY : Divides the query result set into partitions. The window function is applied to each partition separately.
  • ROWSor RANGE clause : Further limits the rows within the partition by specifying start and end points within the partition.

About the author


Leave a Comment