Firstsortedvalue Vs FirstValue Example

    Hi All,

    Please find the example for Firstsortedvalue and FirstValue Example.

     

    EMP:

    LOAD

      EMPNO,

      ENAME,

      JOB,

      MGR,

      Date(Date#(HIREDATE,'DD-MMM-YYYY')) as HIREDATE,

      SAL,

      COMM,

      DEPTNO

    INLINE

    [

      EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO

      7369,SMITH,CLERK,7902,17-Dec-1980,800,NULL,20

      7499,ALLEN,SALESMAN,7698,20-Feb-1981,1600,300,30

      7521,WARD,SALESMAN,7698,22-Feb-1981,1250,500,30

      7566,JONES,MANAGER,7839,02-Apr-1981,2975,NULL,20

      7654,MARTIN,SALESMAN,7698,28-Sep-1981,1250,1400,30

      7698,BLAKE,MANAGER,7839,01-May-1981,2850,NULL,30

      7782,CLARK,MANAGER,7839,09-Jun-1981,2450,NULL,10

      7788,SCOTT,ANALYST,7566,09-Dec-1982,3000,NULL,20

      7839,KING,PRESIDENT,NULL,17-Nov-1981,5000,NULL,10

      7844,TURNER,SALESMAN,7698,08-Sep-1981,1500,0,30

      7876,ADAMS,CLERK,7788,12-Jan-1983,1100,NULL,20

      7900,JAMES,CLERK,7698,03-Dec-1981,950,NULL,30

      7902,FORD,ANALYST,7566,03-Dec-1981,3000,NULL,20

      7934,MILLER,CLERK,7782,23-Jan-1982,1300,NULL,10

    ];

     

     

    Left Join (EMP)

    Load FirstValue(SAL) as FirstValue,

    FirstSortedValue(EMPNO,-SAL) as TopEarned_EMPNO,

    FirstSortedValue(ENAME,-SAL) as TopEarned_ENAME,

    FirstSortedValue(SAL,-SAL) as Top_SAL Resident EMP;

     

     

    DEPT:

    LOAD * INLINE [

     

    DEPTNO,DNAME,LOC

    10,ACCOUNTING,NEW YORK

    20,RESEARCH,DALLAS

    30,SALES,CHICAGO

    40,OPERATIONS,BOSTON

    ];

     

    Courtesy - Manish Kachhia