SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why we use over function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
220 Posts

Posted - 05/04/2013 :  03:27:41  Show Profile  Reply with Quote
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

Edited by - immad on 05/04/2013 03:30:59

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/04/2013 :  23:35:17  Show Profile  Reply with Quote
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.


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




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

India
52325 Posts

Posted - 05/06/2013 :  02:00:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000