Oracle : Multiple aggregate functions on a table

oracleoracle-12c

I have a use case where I need to

  1. Get Max of column DATE
  2. Concatenate all rows in column NAME
  3. Get a value corresponding to the max value of column DATE. This operation has to be done on multiple columns C_A, C_B, C_C ….
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+
| id | DATE        | NAME      | C_A    | C_B    | C_C    | C_D    | C_E    | C_F    | C_G    |
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+
| 1  | 01/01/2018  | JIM       | ROW_A_1| ROW_B_1| ROW_C_1| ROW_D_1| ROW_E_1| ROW_F_1| ROW_G_1|
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+
| 2  | 01/01/2020  | WILLIAM   | ROW_A_2| ROW_B_2| ROW_C_2| ROW_D_2| ROW_E_2| ROW_F_2| ROW_G_2|
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+

Expected output should be 

+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+
| id | DATE        | NAME      | C_A    | C_B    | C_C    | C_D    | C_E    | C_F    | C_G    |
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+
| 1  | 01/01/2020  | WILLIAMJIM| ROW_A_2| ROW_B_2| ROW_C_2| ROW_D_2| ROW_E_2| ROW_F_2| ROW_G_2|
+----+-------------+-----------+--------+--------+--------+--------+--------+--------+--------+

I can do the concatenation by LISTAGG, but I am struggling with the 3rd point. Getting the value corresponding to max DATE. I could do a RANK on each column but considering so many columns doesn't look elegant.

Any help is appreciated.

Best Answer

Option 1: You could write one subquery to aggregate the names, one subquery to find the max date, then join the second subquery against the original table on the date to get the rest of the C_ columns. This is ugly.

Option 2: You could use Analytic Functions:

SELECT MAX(date_col) AS max_date_col,
       LISTAGG(name) WITHIN GROUP (ORDER BY name ASC) AS names,
       MAX(c_a) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_a,
       MAX(c_b) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_b,
       MAX(c_c) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_c,
       MAX(c_d) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_d,
       MAX(c_e) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_e,
       MAX(c_f) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_f,
       MAX(c_g) KEEP (DENSE_RANK FIRST ORDER BY date_col DESC) AS c_g
  FROM /* ... */
Related Question