over() keyword

what are the uses of Over() keyword and what are the different options available? In what situations do you use this?
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Over() is used to partition data

Ie...

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.
Deepak ChauhanSQL Server DBACommented:
Hi

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.

https://msdn.microsoft.com/en-us/library/ms189461.aspx
Éric MoreauSenior .Net ConsultantCommented:
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?
chaauCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jyozeal, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.