5 Replies Latest reply: Jan 10, 2011 3:20 AM by Nimish Garg RSS

    How to use "Rank" function  in Oracle?

    francislazaro
      I need to display Top 15 records by using rank function.

      Here is my query...I need to pull top 15 FAQ's using the below query.. How can I use RANK function to display the Top 15 FAQ"s in the list.


      Select  distinct SUb1.FAQ,Sub1.FAQ_Hits,GU.display_Name_FMLS as displayname,ev.ParentLinkrecordid,ev.userid from User GU
      Join Event ev
      ON LOWER (ev.userid) IN (LOWER (GU.lanid), LOWER (Gu.racfid))
      Join (Select distinct sm.stem as FAQ,Sum(ev.Eventresults) as FAQ_Hits,ev.ParentLinkrecordid as Topic_ID from Event ev
      Join SubjectMatter sm
      ON (TO_CHAR (sm.smrecordid) = ev.eventdetail1) AND ev.eventdetail1 IS NOT NULL AND sm.smtype = 1 
      Where (Upper(ev.eventsubtype) in (Upper('FAQ'),Upper('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
      ON Sub1.Topic_ID = ev.ParentLinkrecordid)
        • 1. Re: How to use "Rank" function  in Oracle?
          Hoek
          http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
          • 2. Re: How to use "Rank" function  in Oracle?
            696240
            I could provide a more helpful answer if you provided sample data (i.e. a create table and insert statements), but here's a generic version of what you want to do:
            SELECT     ename
            ,     sal 
            FROM     (
                 SELECT     ename
                 ,     sal
                 ,     RANK() OVER (ORDER BY sal DESC) sal_rank
                    FROM emp
                 ) 
            WHERE sal_rank <= 15;
            • 3. Re: How to use "Rank" function  in Oracle?
              Madhu BR
              A few bits that I noticed in the query ...
               in (Upper('FAQ'),Upper('OPENFAQ'))
              1) Do you really a upper for a string which is already in upper case.
              Select distinct sm.stem as FAQ,Sum(ev.Eventresults) as FAQ_Hits,ev.ParentLinkrecordid as Topic_ID
              2) Do you need a distinct when you are using a GROUP function viz. SUM ?

              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.
              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;
              Like mentioned above, some sample data would have helped.
              • 4. Re: How to use "Rank" function  in Oracle?
                732302
                Can Rank() be used with non numeric fileds like customer name?
                • 5. Re: How to use "Rank" function  in Oracle?
                  Nimish Garg
                  check these links

                  http://nimishgarg.blogspot.com/2010/02/oracle-sql-rank-denserank-rownumber.html

                  http://nimishgarg.blogspot.com/2010/06/oracle-simulating-rank-analytical.html


                  Nimish Garg
                  http://nimishgarg.blogspot.com/