Over() function in SQL server is same as Oracle?

Question

Hi ,

I am migrating from Oracle to SQL , i do see there are few mismatch due to Over() in Oracle,

i dont see any compile error but mismatch, what is difference between oracle Over and SQl Over,

how i can fix.

Thanks

A-

---

Ashok

Answer 1

Hi

OVER IN T-SQL :

OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.....

http://msdn.microsoft.com/en-us/library/ms189461.aspx

You can try where difference of Syntax between TSQL and PL SQL is described:

http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 2

They are almost equivalent between T-SQL at SQL Serve side and PL/SQL at Oracle but can you share how you are using Over function at T-SQL to check it along with PL/SQL.. 

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

Answer 3

Here is a sample query using Over function-

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", 
    p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0
ORDER BY PostalCode;
GO
-------------------------------
Here is the result set.
Row Number      LastName                SalesYTD              PostalCode
--------------- ----------------------- --------------------- ----------
1               Mitchell                4251368.5497          98027
2               Blythe                  3763178.1787          98027
3               Carson                  3189418.3662          98027
4               Reiter                  2315185.611           98027
5               Vargas                  1453719.4653          98027
6               Ansman-Wolfe            1352577.1325          98027
1               Pak                     4116871.2277          98055
2               Varkey Chudukatil       3121616.3202          98055
3               Saraiva                 2604540.7172          98055
4               Ito                     2458535.6169          98055
5               Valdez                  1827066.7118          98055
6               Mensa-Annan             1576562.1966          98055
7               Campbell                1573012.9383          98055
8               Tsoflias                1421810.9242          98055
Reference : http://msdn.microsoft.com/en-us/library/ms189461.aspx

Answer 4

The OVER() function in T-SQL follows the ANSI standard, however not everything is implemented, and it is possible that Oracle is more versatile. To my knowing, Microsoft has no proprietary extensions.

Also, beware that SQL 2012 added support for windowed aggregates; this is misisng from SQL 2008.

As for "how can i fix"? I have no idea! I mean, I don't know what you have problem with!

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Here is how OVER can be used in SQL Server:

http://www.sqlusa.com/bestpractices2005/overpartitionby/

In SQL Server 2012, you can use most aggregate functions with OVER:

USE AdventureWorks2012;
GO

SELECT 
	YEAR(OrderDate) AS SalesYear
   ,AVG(SUM(Subtotal)) OVER (ORDER BY YEAR(OrderDate) 
                                            ) AS MovingAvg
   ,SUM(SUM(Subtotal)) OVER (ORDER BY YEAR(OrderDate)  
                                            ) AS CumulativeTotal
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY SalesYear;


SalesYear	MovingAvg	CumulativeTotal
2005		11331808.96	11331808.96
2006		21003291.07	42006582.14
2007		28006391.29	84019173.88
2008		27461984		109847936

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012