How to Order by in listagg-CodePudding

If I write a simple query, then orakle gives the correct data

SELECT NUMBER FROM myNumbers WHERE user = '11111' ORDER BY length(NUMBER) ASC;

but I need to use the listagg function, in order to continue to throw several options of numbers. my new statement looks like this

SELECT listagg(NUMBER, ', ') WITHIN GROUP (ORDER BY LENGTH(NUMBER)  ASC) AS MANY_NUMBERS
FROM myNumbers WHERE user = '11111'' AND ROWNUM < 4;

but in this Statement the ORDER BY does not work. The data is not "sorted" and the data is not displayed correctly

CodePudding user response：

Queries you used aren't the same.

First query should be an inline view for the second, because the 2nd doesn't have ORDER BY clause (as the 1st one) so ROWNUM < 4 returned who-knows-which rows.

SELECT listagg(NUMBER, ', ') WITHIN GROUP (ORDER BY LENGTH(NUMBER)  ASC) AS MANY_NUMBERS
from (SELECT NUMBER FROM myNumbers WHERE user = '11111' ORDER BY length(NUMBER) ASC)
where rownum < 4;

Or, I'd rather suggest something like this (I rename columns as names you used are reserved for datatype and Oracle function):

SQL> with mynumbers (cuser, num) as
  2    (select '11111', 8246    from dual union all
  3     select '11111', 8246    from dual union all
  4     select '11111', 5002647 from dual union all
  5     select '11111', 5002748 from dual union all
  6     select '11111', 5002750 from dual union all
  7     select '11111', 5003078 from dual union all
  8     select '11111', 5002226 from dual
  9    ),
 10  temp as
 11    (select num,
 12            row_number() over (partition by cuser order by length(num), num) rn
 13     from mynumbers
 14     where cuser = '11111'
 15    )
 16  select listagg(num, ', ') within group (order by rn) many_numbers
 17  from temp
 18  where rn < 4;

MANY_NUMBERS
--------------------------------------------------------------------------------
8246, 8246, 5002226

SQL>