How OVER clause works ?

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  November 13, 2017 at 4:29 am

  #333637

  Hi All,

  Today, I came across tsql code which is been used in our environment (Sql 2014). Its an enhancements done for OVER Clause.
  Its a windowing function. I am getting what I need, but trying to visualize things here..what happening behind the scenes.  

  Anyone used these function??
  MAX(LEFT(c1, 1)) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING)

  Basically, want to understanding how the MaxVal is being derived for each row. Can anyone give a short description if you have used this earlier??

  create table test
  (id int identity(1,1),
  c1 varchar(10)
  )

  insert into test
  select '0'

  union all
  select '1'
  union all
  select '2'
  union all
  select '3'
  union all
  select '4'
  union all
  select '5'
  union all
  select '6'

  union all
  select '1'
  union all
  select '2'
  go

  select
      id,        
      c1,
      ascii(c1) as AsciiValue,
      MAX(LEFT(c1, 1)) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING)  as MaxVal
  from test;
  go

  
  drop table test;

  Thanks,
  Sam

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 13, 2017 at 4:53 am

  #1967740

  Sam

  It's taking the maximum value of c1 over the current row and the next five, ordered by id.  So for row 1, it will show the maximum value from rows 1, 2, 3, 4, 5 and 6. 

  John

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 13, 2017 at 4:55 am

  #1967741

  MAX(LEFT(c1, 1)) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING)
  Let's split this into several sections:
  LEFT(c1, 1)
  Returns the left most x character(s) from a string. The string is your first parameter (c1 in your case) and x is the second parameter (1).  So this returns the left most character from your column c1. For example, for the string '123456' then '1' would be returned, for the string 'ashdva' then 'a' would be returned.
  MAX()
  Returns the maximum value for an expression (contained in the brackets). This can change a little depending on the data type. For example if you're using an integer then out of the numbers 1, 5, 9, 12 then the MAX value would be 12. If, however, they were strings then '9' would be returned.

  The OVER is because we aren't grouping, so it enables us to use MAX as a WINDOW funciton
  ORDER BY id ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING
  Now, this bit does several things. Firstly, within the WINDOW function the order of the resultset is ordered by the value of your column id (in ascending order). Then, the ROW BETWEEN part is used to limit what rows are used within the window function. Those rows are difined at the CURRENT ROW (that one should be self explanatory) and the 5 row following; so only the next 5 rows after that row, if they are sorted by the column id.

  So, in your dataset, for your first row  the MAX value is 5. That's because for the first row it only uses ids 1 to 6 to determine the MAX value. That's 5 (because 6 doesn't appear till row 7). Every row doesn't use any rows prior to it to determine the MAX value; thus when the value of c1 drops (at id 8) the MAX is 2.

  I appreciate my explanation might be alittle "rough around the edges" (other can likely give a better one than I! :)). This is all explained in the documentation of the OVER Clause (Transact-SQL), so have a look. There are other qualifiers apart from BETWEEN, FOLLOWING and CURRENT ROW, which are PRECEDING and UNBOUNDED.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  November 13, 2017 at 5:38 am

  #1967752

  Thanks John and Tom. Thank you for the detailed explaination.