Oracle uses listagg to sort wM_concat functions

Problem: Oracle sorts queries by default when using in. Unable to print in condition order for example:

/* id sname 1 AA 1239 BB 2 CC */
select sname from user where id in (1.1239.2)
-- Expectation: AA BB CC
-- Result: AA CC BB
Copy the code

You can see that the query results are sorted by criteria, so you can improve the code and use decode to sort

select sname from user where id in (1.1239.2) ORDER BY DECODE(id, 1.1239.2)
The result is AA BB CC
Copy the code

You can see that decode can solve the sorting problem caused by in operation!

But! When in a scenario using the wM_concat function

select wmsys.wm_concat(sname) from user where id in (1.1239.2) ORDER BY DECODE(id, 1.1239.2)
-- Result: AA,CC,BB
Copy the code

There’s another problem with sorting… The reason was that the wm_concat function itself sorted the result set once, and was deprecated in Oracle 12C, so the idea was to use listAGg instead of wm_concat

Listagg () takes two arguments, an aggregate column and a delimiter, and concatenates them without delimiters.

Within group(order by xx) order by xx


select listagg(sname, ', ') within group(order by decode(id,1.1239.2) desc) 
from (
select id,sname from user where id in (1.1239.2) - order by decode desc (id, 1123, 9, 2)

)
-- Result: AA,CC,BB
Copy the code

Within group() does not support this, so try to sort the inner query and use rownum to identify the sort

select listagg(sname, ',') within group(order by rownum) from (select id,sname from user where iid in (1,1239,2) order by decode(id,1,1239,2) Desc) -- Result: Select listagg(sname, sname, ',') within group(order by rownum) from (select id,sname from user where iid in (1,1239) order by decode(id,1,1239) Desc) -- result: CC,AACopy the code

Discover still have a problem! Try instr

select listagg(sname, ',') within group(order by rownum) from (select id,sname from user where iid in (1,1239) order by instr('1,1239',iid)) -- result CC,AA -- Considering id value comma, Select listagg(sname, ',') within group(order by rownum) from (select id,sname from user where iid in (1,1239) order by Instr (', 1123, 9 ', ', '| | iid | |', ')) - the results AA, CCCopy the code

This is finally settled

Summary: Order by instr() is used for sorting in the in operation scenario, which can be output in the order of in conditions. Use listagg instead in the WM_concat scenario.