Analytic functions in Netezza

Analytic functions:-

Below are the Analytic function available in Netezza

1) ROW_NUMBER(), 

2) RANK ()

3) DENSE_RANK ()

4) FIRST()

5) LAST()

6) LEAD() and LAG()

The advantages of Analytic function over Group by Function.

Difference:- 

GROUP BY Function

The group by function allow us to select  columns which are part of group by and column on which aggregation is performed. 

I want to display maximum salary employee from each dept.

Query and result:-

Here is the Question i want to know the Employee name as well along with max sal and in which dept he works for.

Query and result:-

As we discussed we can't use Ename as its is not part of group by nor we are doing aggregation on this column.

Lets see what flexibility we will get by using Analytic functions.

SELECT maxsal, 
       deptno, 
       ename 
FROM   ( 
                SELECT   Max(sal) OVER (partition BY detpno ORDER BY sal)        AS maxsal, 
                         row_number over (partition BY detpno ORDER BY sal DESC) AS id, 
                         ename, 
                         deptno 
                FROM     emp) 
WHERE  id=1;

just place id=2 instead of id=1 for second max salary 
if ur are expecting more than 1 records with max sal 
replace ROW_NUMBER with DENSE_RANK()

Please refer the below site to know more about Analytic function.

http://www.oracle-developer.net/display.php?id=102