0

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.

1

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 /* ... */
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.