Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why we use over function

Author  Topic 

immad
Posting Yak Master

230 Posts

Posted - 2013-05-04 : 03:27:41
can any one explain me in easy words with easy example that why we use
over() function in sql server 2005 query
Thanks in advance

immad uddin ahmed

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-04 : 23:35:17
OVER() Clause allows you to specify more restrictions on the set you want to operate on:
For example:
In the OVER() Clause below, we are grouping the data using Field 2 hence all the rows with 'Cat' are given consecutive row_numbers; and all the rows with 'Dog' are grouped together and get different set of row numbers.

[CODE]
CREATE TABLE dbo.TestStub (
ID INT,
Field1 NVARCHAR(20),
Field2 NVARCHAR(20),
Field3 NVARCHAR(20)
);
INSERT INTO dbo.TestStub(ID, Field1, Field2, Field3) VALUES
(101,'Test', 'Cat', 'John'),
(220, 'Release', 'Cat', 'Jone'),
(380,'Production', 'Cat', 'Jen'),
(451, 'Test', 'Dog', 'Jim'),
(562,'Release', 'Dog', 'Jane'),
(667, 'Production', 'Dog', 'Jill');

SELECT ROW_NUMBER() OVER(PARTITION BY Field2 Order by ID) AS RowN, ID, Field1, Field2, Field3 from dbo.TestStub;

RowN ID Field1 Field2 Field3
1 451 Test Dog Jim
2 562 Release Dog Jane
3 667 Production Dog Jill
1 101 Test Cat John
2 220 Release Cat Jone
3 380 Production Cat Jen

[/CODE]


Here is nice article that describes the OVER() Clause
http://msdn.microsoft.com/en-us/library/ms189461.aspx


After reading the above article if you have more specific questions, feel free to post them here.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 02:00:25
quote:
Originally posted by immad

can any one explain me in easy words with easy example that why we use
over() function in sql server 2005 query
Thanks in advance

immad uddin ahmed


OVER clause allows you to vertically group data based on column or group of columns. This is particulary useful when you want to do some custom aggregation or analytic function calculation within the group nad based on some predefined order like ranks within group, sum of quantity within group etc.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -