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

  • 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

    Friday, December 28, 2012 7:34 PM

Answers

All replies

  • 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/


    Friday, December 28, 2012 7:44 PM
  • 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

    Friday, December 28, 2012 7:52 PM
  • 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

    Friday, December 28, 2012 8:32 PM
  • 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, esquel@sommarskog.se
    Friday, December 28, 2012 8:56 PM
  • 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





    • Edited by Kalman Toth Saturday, December 29, 2012 12:16 AM
    • Marked as answer by Jumpingboy Sunday, December 30, 2012 5:31 PM
    Friday, December 28, 2012 9:06 PM