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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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
);
Premium Content
You need an Expert Office subscription to comment.Start Free Trial