We help IT Professionals succeed at work.

over() keyword

what are the uses of Over() keyword and what are the different options available? In what situations do you use this?
Watch Question

Over() is used to partition data


Row_number() over (partition by col1,col2 order by col3 desc)

Col3 usually a date column used to order data. Code above will  group data by col1 and col2 and assign it an order value 1,2,3... The order value determined by col3.

You could do one column instead of 2. One is minimum.

I always use this Clause with row_number function and this can be apply to other functions as well.

Please check the below microsoft link for the overall information with examples.

Éric MoreauSenior .Net Consultant
Top Expert 2016

Over is to partition your data but you have to be very cautious as over the versions of SQL, the results are different.

So which version are you using and with which statement (SUM, ...) do you want to use it?
Top Expert 2013
I must add to contrary to the previous answers the partitioning of the data is optional depending on the Window function used, i.e. you could have just the order by clause inside the OVER(), you can have nothing at all, like this:
select t, d,
count(*) over() cnt,
row_number() over(order by t) rn_by_t,
row_number() over(order by d) rn_by_d,
max(d) over(partition by t) max_by_t,
sum(d) over() total_d
from t

Open in new window

SQL Fiddle
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Jyozeal, do you still need help with this question?