DB2 SQL Select top x of one column and top y of another column into one row. LISTAGG?

user_19887193 0
Viewed 35

Using DB2 SQL, I'm trying to take data in a format like this:

a b c
N 25 300
Y 48 290
N 40 280
N 30 268
Y 26 264
N 40 256
N 38 253
N 33 251
Y 44 247
N 64 236
N 30 226

I want a list of the b values of the rows with the highest c values, the top 3 where a = N and top 2 where a = Y. And within the list, I'd like the values sorted by b ascending.

Would like to get it down to one row something like this using LISTAGG:

N = 25, 30, 40; Y = 26, 48

Can anyone help? Our shop is just now turning on FL 501 and I'm still learning how to best use LISTAGG.

Thanks!

listagg db2 sql
1 Answers

Try this:

WITH TAB (a, b, c) AS
(
          SELECT 'N', 25, 300 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Y', 48, 290 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 40, 280 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 30, 268 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Y', 26, 264 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 40, 256 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 38, 253 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 33, 251 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Y', 44, 247 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 64, 236 FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'N', 30, 226 FROM SYSIBM.SYSDUMMY1
)
SELECT LISTAGG (LST, '; ') AS RES
FROM
(
  SELECT 
     A 
  || ' = ' 
  || LISTAGG (B, ', ') WITHIN GROUP (ORDER BY B)
     AS LST
  FROM
  (
    SELECT 
      T.*
    , ROW_NUMBER () OVER (PARTITION BY A ORDER BY C DESC) AS RN_
    FROM TAB T
  ) T
  WHERE A = 'N' AND RN_ <= 3 OR A = 'Y' AND RN_ <= 2
  GROUP BY A
) T
RES
N = 25, 30, 40; Y = 26, 48
user_104182640
Related