ListAgg
LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.
ListAgg syntax:
LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
Examples:
-- LISTAGG ALL EMPLOYEES IN A GROUP ORDERED BY SALARY DESC
SELECT LISTAGG(FIRST_NAME || ' ' || LAST_NAME , ';') WITHIN GROUP(ORDER BY SALARY DESC) FROM EMPLOYEES
-- LISTAGG ALL EMPLOYEES IN A GROUP ORDERED BY YEAR
SELECT LISTAGG(FIRST_NAME || ' ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE),
TO_CHAR(HIRE_DATE, 'YYYY') HIRE_DATE FROM EMPLOYEES WHERE DEPARTMENT_ID=60
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
-- LISTAGG EMPLOYEES, GROUP BY DEPARTMENT_ID
SELECT DEPARTMENT_ID,
LISTAGG(FIRST_NAME || ' ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID
-- LISTAGG EMPLOYEES USED AS AN ANALYTIC FUNCTION
SELECT DEPARTMENT_ID,
FIRST_NAME,
LAST_NAME,
LISTAGG(FIRST_NAME || ' ' || LAST_NAME , ';') WITHIN GROUP (ORDER BY HIRE_DATE) OVER (PARTITION BY department_id) as EMPLOYEES_WITHIN_DEPARTMENT
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID
NTH_VALUE
NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause. The returned value has the data type of the measure_expr.
You can now obtain the nth row in a window. NTH_VALUE is an addition to the already existing analytic functions: FIRST_VALUE and LAST_VALUE
Example:
-- SELECT EMPLOYEES ORDER BY DEPARTMENT_ID, HIRE_DATE AND ASSIGN A ROW NUMBER
SELECT DEPARTMENT_ID, HIRE_DATE, FIRST_NAME, LAST_NAME,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID, HIRE_DATE) ROW_NUMBER FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, HIRE_DATE
Now, if we wanted the 2nd value in the above window, we could use nth_value function as follows:
-- SELECT 2ND EMPLOYEE ORDER BY HIRE_DATE
SELECT DEPARTMENT_ID, HIRE_DATE, FIRST_NAME, LAST_NAME,
ROW_NUMBER() OVER(PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID, HIRE_DATE) ROW_NUMBER ,
NTH_VALUE(FIRST_NAME || ' ' || LAST_NAME, 2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) NTH_VALUE
FROM EMPLOYEES
