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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you please post the current results and expected results in the excel file?
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.