oracle listagg the outcome is too long

Question

I am using listagg() to aggregate string values

For instance, there is a table like this:

Table1
Name     Rank
Bob      A
Bob      B
Bob      C
Tom      A
Tom      C

SELECT Name,LISTAGG(RANK,';') WITHIN GROUP (ORDER BY RANK) AS COMRANK 
FROM Table1

and we get result:

Name  COMRANK
Bob   ABC
Tom   AC

What if COMRANK becomes too long? Could I find top xxx before I aggregate them?

Answer 1

You can rank the rows using window function and limit the rows upto whichever rank you need.

SELECT Name,LISTAGG(RANK,';') WITHIN GROUP (ORDER BY RANK) AS COMRANK 
FROM (select
    name, rank,
    rank() over (partition by name order by rank) rnk
  from Table1
) where rnk < 10; --some value
GROUP BY Name;