Your question, your audience. Choose who sees your identity—and your question—with question security.
SELECT
'PAGES' AS Bucket
, FUNC_OMNI_DEPT_PAGE_OWNER(a.PAGE_ID) AS Topic
, 'CONTENT_CHANGE' AS subTopic
, PAGE_TITLE AS Headline
, 'web page content changed on ' || LAST_UPDATE AS Summary
, L.link_id
FROM OMNI_ENT_PAGES
-- sometable with the alias a
INNER JOIN (
SELECT link_id, link_url, row_number() over (partition by a.page_ID order by link_url) as row_ref
FROM omni_ent_link
WHERE link_type = 'INTRANET'
) as L ON L.row_ref = 1 AND L.link_url LIKE '%?page=' || a.PAGE_ID
but as I do not know where the alias a comes from I've used a crystal balldrop table my_pages purge;
create table my_pages (
page_id number,
last_update date
);
insert into my_pages values(650023,sysdate);
insert into my_pages values(650016,sysdate);
insert into my_pages values(111111,sysdate);
commit;
drop table my_links purge;
create table my_links (
link_id number,
link_url varchar2(100),
last_update date
);
insert into my_links values(10190,'../../Goto/Page.aspx?page=650023',sysdate);
insert into my_links values(10191,'../../Goto/Page.aspx?page=650023',sysdate-1);
insert into my_links values(10056,'Page.aspx?page=650016',sysdate);
insert into my_links values(10000,'bob',sysdate);
commit;
select page_id, last_update, link_id
from my_pages,
(
select link_id, link_url, regexp_substr(link_url,'[0-9]+$') url_page_id,
row_number() over(partition by regexp_substr(link_url,'[0-9]+$') order by last_update desc) myrn
from my_links
) my_link_view
where my_pages.page_id = my_link_view.url_page_id and myrn=1
/
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.
From novice to tech pro — start learning today.
Open in new window
in the subquery I take the last piece of the link_url (after '?page=') and then use this as the joining field to Arow_number() is used to emulate the "and rownum = 1" of the original. i.e. only the first matching record is returned for each page_id