LISTAGG

This is one of the numerous functions that I was not aware of until today. LISTAGG orders data within each group and concatenates the values of the measure column. The keyword here is concatenates.

For example, you are required to display all the employees within a department in one comma separated row item.

SELECT DISTINCT NVL(department_id, '0') Department_ID,
listagg(first_name, ',') within GROUP (
ORDER BY first_name) over (partition BY department_id)
FROM employees
ORDER BY 1;

The ORDER BY first_name is to order the data within each group specified by the OVER clause. The data is partitioned by department_id and then sorted on first_name. The output to this query is as follows –

The same query can also be written without the OVER clause by simple using GROUP BY –

SELECT NVL(department_id, 0) Department_ID,
listagg(first_name, ',') within GROUP (
ORDER BY first_name) Employees
FROM employees
GROUP BY department_id
ORDER BY 1;

 

RANK vs DENSE_RANK

Both RANK and DENSE_RANK can be used as an aggregate and analytic function. When used as an aggregate function, both calculate the rank of the hypothetical value passed as parameter to the function with respect to (or can we say WITHIN GROUP) the aggregate group and the sort order. And when used as an analytic function, both compute the rank of each row returned with respect to (or can we say OVER) other rows.

The OVER in the select statement tells Oracle that the function is an analytic function and not the usual aggregate function. Without the PARTITION BY clause, the function acts on the entire record set returned by the where clause.

The difference between RANK and DENSE_RANK is:

RANK – Rows with equal values for the ranking criteria receive the same rank, as shown below –

SELECT department_id,

 last_name,

 salary,

 rank() over (partition BY department_id order by salary) AS rank1

FROM employees

WHERE department_id IN (50)

ORDER BY rank1;

DENSE_RANK – The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. The largest rank value is the number of unique values returned by the query.

SELECT department_id,

last_name,

salary,

dense_rank() over (partition BY department_id order by salary) AS drank

FROM employees

WHERE department_id IN (50)

ORDER BY drank;