T-SQL Tuesday: The OVER Clause

Not quite...

The OVER clause was first introduced to us in SQL 2005. It is often used as a supplement to other functions to provide which rows from a particular query are applied to a function. It is usually presented as part or in addition to an existing result set.

There are a few parts to the OVER() clause. ORDER BY (mandatory), PARTITION BY (optional, often used to "restart" numbering or alike), and Framing (we will discuss this in another blog post).

Quick example would be if I want to create a row number for a temp table I just made so I have something to run a loop against:

SELECT [RowNumber] = rank() OVER (ORDER BY name), DBName = [Name] INTO #DBLoop FROM sys.databases

I use the rank function in conjunction with the OVER() clause.

The uses for the over clause are many. We can partition the row number and make it restart the numbering based on whatever criteria we deem necessary.

I will go in more depth in future posts.

#sqlserver #TSQL #Devleopment