• Facebook - Black Circle
  • Twitter Round
  • Instagram Black Round
  • LinkedIn - Black Circle

SEARCH BY TAGS: 

Please reload

RECENT POSTS: 

FOLLOW ME:

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle

T-SQL Tuesday: The OVER Clause

June 13, 2017

 

 

 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. 

Share on Facebook
Share on Twitter
Please reload