This content has been marked as final. Show 5 replies
A few bits that I noticed in the query ...
1) Do you really a upper for a string which is already in upper case.
2) Do you need a distinct when you are using a GROUP function viz. SUM ?
Select distinct sm.stem as FAQ,Sum(ev.Eventresults) as FAQ_Hits,ev.ParentLinkrecordid as Topic_ID
You rank query is as follows, I am not very good at the ANSI style JOIN so changed it slightly ... :-)
Also notice the usage rank function in the "sub1" query.
Like mentioned above, some sample data would have helped.
select distinct sub1.faq, sub1.faq_hits, gu.display_name_fmls as displayname, ev.parentlinkrecordid, ev.userid from user gu, event ev, (select rank() over (order by sum(ev.eventresults) desc) rnk, sum(ev.eventresults) as faq_hits, sm.stem as faq, ev.parentlinkrecordid as topic_id from event ev, subjectmatter sm where (to_char(sm.smrecordid) = ev.eventdetail1) and ev.eventdetail1 is not null and sm.smtype = 1 AND upper(ev.eventsubtype) in ('FAQ', 'OPENFAQ') and to_date(eventdatetime, 'yyyy-mm-dd hh24:mi:ss') >= to_date('20100601', 'yyyymmdd') and to_date(eventdatetime, 'yyyy-mm-dd hh24:mi:ss') <= to_date('20100831', 'yyyymmdd') group by sm.stem, ev.parentlinkrecordid order by faq) sub1 where lower(ev.userid) in (lower(gu.lanid), lower(gu.racfid)) and sub1.topic_id = ev.parentlinkrecordid) and sub1.rnk <= 15;