Oracle's Analytical functions Vs SQL Server's Windowing Functions

With the release of SQL Server 2012 , SQL Server has caught up with Oracle's implementation of Analytical functions , there are few subtle differences .

We cannot use a constant in the windowing clause . The workaround is to do declare/ select the constant in the Windowing function.

Here is an example.

ORACLE
======

SQL> drop table emp;
Table dropped.

SQL> Create table emp
  2  ( eid int primary key,
  3    elname  char(10 ) not null,
  4    efname  char(10 ) not null,
  5    deptname char(10)
  6  ) ;
Table created.

SQL> insert into emp values ( 100 , 'Abdul' , 'Hameed' , 'Sales');
1 row created.

SQL> insert into emp values ( 200 , 'Syed' , 'Ishak' , 'Sales');
1 row created.

SQL> insert into emp values ( 300 , 'Zahir' , 'Mohideen' , 'IT');
1 row created.

SQL> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by 1 ) rn from emp;

       EID ELNAME     EFNAME             RN
---------- ---------- ---------- ----------
       300 Zahir      Mohideen            1
       100 Abdul      Hameed              1
       200 Syed       Ishak               2


SQL Server
==========

C:\>sqlcmd -W
1> use test2012nf
2> go
Changed database context to 'test2012nf'.
1> drop table emp;
2> GO
1> Create table emp
2> ( eid int primary key,
3>   elname  char(10 ) not null,
4>   efname  char(10 ) not null,
5>   deptname char(10)
6> ) ;
7> GO
1> insert into emp values ( 100 , 'Abdul' , 'Hameed' , 'Sales');
2> insert into emp values ( 200 , 'Syed' , 'Ishak' , 'Sales');
3> insert into emp values ( 300 , 'Zahir' , 'Mohideen' , 'IT');
4> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
1> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by 1 ) rn from emp;
2> go
Msg 5308, Level 16, State 1, Server ITSQL-012, Line 1
Windowed functions and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.


Now replace

1> Select eid , elname , efname , ROW_NUMBER() over ( partition by deptname  order by (select 1)  ) rn from emp;
2> go
eid elname efname rn
--- ------ ------ --
300 Zahir      Mohideen   1
100 Abdul      Hameed     1
200 Syed       Ishak      2

(3 rows affected)

Comments Welcome!

Analytics 2.0 - Part II

Yet another neat addition to the family of Analytics in 11g Release 2.
With this new function , I can look up the value of nth row .

The following gives the 4th salary from the dept 30 ( look at the column nv) .

1 SELECT empno,
2 ename,
3 deptno,
4 sal,
5 first_value(sal) over(PARTITION BY deptno ORDER BY empno) fv,
6 nth_value((sal), 4) over(PARTITION BY deptno ORDER BY empno) nv,
7 last_value(sal) over(PARTITION BY deptno ORDER BY empno
8 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
9 FROM emp
10* WHERE deptno = 30


SQL> /


EMPNO      ENAME      DEPTNO     SAL        FV        NV          LV
---------- ---------- ---------- ---------- ---------- ---------- ----------
7499       ALLEN       30        1600        1600                  950
7521       WARD        30        1250        1600                  950
7654       MARTIN      30        1250        1600                  950
7698       BLAKE       30        2850        1600        2850      950
7844       TURNER      30        1500        1600        2850      950
7900       JAMES       30         950        1600        2850      950


6 rows selected.

Good luck ...