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.
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 useover() function in sql server 2005 queryThanks in advanceimmad 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 Field31 451 Test Dog Jim2 562 Release Dog Jane3 667 Production Dog Jill1 101 Test Cat John2 220 Release Cat Jone3 380 Production Cat Jen[/CODE]Here is nice article that describes the OVER() Clausehttp://msdn.microsoft.com/en-us/library/ms189461.aspxAfter reading the above article if you have more specific questions, feel free to post them here. |
|
|
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 useover() function in sql server 2005 queryThanks in advanceimmad 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|