rownumber inside listagg

Hi Team,

Below is my query
select LISTAGG(name, chr(10)) WITHIN GROUP (ORDER BY name),ORGANIZER_EMAIL from community_call_details where RECORDING_LINK is null and trunc(sysdate )  > call_date + 2
group by ORGANIZER_EMAIL

This retrieves results like below. I have ignored the other column just posting the results of first column

Cloud Accelerate Community Call Competitive Update for PaaS for SaaS Content & Experience Cloud (CEC) 17.3.5 & 17.4.1 Proficiency Webcast Graph Enthusiast Community Call NATD Cloud Community Call NATD Cloud Community Call NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD iPaaS Office Hours Call OOW 2017 Announcements to Accelerate Sales & Create New Opportunities Office Hours - Chatbots Personal Assistants - Cutting Edge Content Delivery w/ CEC Oracle Management Cloud - OOW 2017 Key Insights & Highlights PaaS OOW highlights from AppDev, Integration, and much more Preview of Oracle Autonomous Database SE Community Call for Architects SE Community Call for Architects Town Hall for Oracle Detroit and Ann Arbor Employees teast teast

But I want the results to be numbered. Is this possible?

like below

1.Cloud Accelerate Community Call Competitive Update for PaaS for SaaS Content & Experience Cloud (CEC) 17.3.5 & 17.4.1 2.Proficiency Webcast Graph Enthusiast Community Call NATD Cloud Community Call NATD Cloud Community Call NATD Universal 3.Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open 4.Office Hours NATD Universal Credits & BYOL Open Office Hours NATD Universal Credits & BYOL Open Office Hours NATD iPaaS 5.Office Hours Call OOW 2017 Announcements to Accelerate Sales & Create New Opportunities Office Hours - Chatbots Personal 6.Assistants - Cutting Edge Content Delivery w/ CEC Oracle Management Cloud - OOW 2017 Key Insights & Highlights PaaS OOW 7.highlights from AppDev, Integration, and much more Preview of Oracle Autonomous Database SE Community Call for Architects 8.SE Community Call for Architects Town Hall for Oracle Detroit and Ann Arbor Employees teast teast
sakthikumarAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
You would get your questions answered much faster if you ask Oracle related questions in the Oracle Topic Area.  Many of us don't monitor the other database related areas.


Generate the row_number in an inline view and generate the listagg in the outer query.

Simple test case:
/*
drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('b');
insert into tab1 values('a');
insert into tab1 values('c');
commit;
*/

select listagg(rn || '. ' || col1, chr(10)) within group (order by col1) 
from (
	select col1, row_number() over(order by col1) rn
	from tab1
);

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
can you please post the current results and expected results in the excel file?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here