Oracle – Top N Records

Background

This last week, Mr. P and I were talking shop as to how difficult it can be to return “Top N Records” from a Query.

In SQL Server, it is not that difficult we simply use select top N.

For more difficult scenarios where grouping is needed, we use Windowing Functions.

 

Oracle

He emailed me what he came up with.  And, I promised to see how badly I will stumble.

Now that I discovered Oracle Live SQL, I know that I don’t even have to request access to our in-house Oracle database, I can just use a free Cloud DB Playground.

Oracle Live SQL

Table & Data

As always, I need a test table and some data.

Found out that Oracle Live SQL provides two schemas, HR for small form factors.

And, Sales History for bigger data.

Metadata

Let us see which tables are availed within the HR schema.


select 
          tbl.OWNER
        , tbl.TABLE_NAME
        , tbl.TABLESPACE_NAME
        , tbl.STATUS
        , tbl.SAMPLE_SIZE
        , tbl.LAST_ANALYZED
        
from   ALL_TABLES tbl

where tbl.OWNER in ('HR')

Output

 

Sample Queries

From Googling, found some good Q/A on StackOverflow.

Outline

They directed me at the following:

  1. Fetch First
  2. Analytic Function
  3. ROWNUM

 

Query – Get all Data

From reviewing ALL_TABLES, I know that I have only 10 records in the HR.JOB_HISTORY table.  And, so it is OK to simply return all records and order by specific ranking columns.

 


select 

         tblJH.END_DATE as dateEnd
       , tbLJH.START_DATE as dateStart
       , tblJH.EMPLOYEE_ID as employeeID
       , tblJH.JOB_ID as jobID
       , tblJH.DEPARTMENT_ID as departmentID

from   HR.JOB_HISTORY tblJH

order by
      tblJH.END_DATE desc

 

Output

 

Explanation

  1. Returns 10 records

 

Query – Fetch First

Fetch_First needs an ordered inner list.
And, within the First_First clause specify number of records.

select * 
 
from 
 
    ( 
          select  
              tblJH.END_DATE as dateEnded 
            , tblJH.START_DATE as dateStarted   
            , tblJH.EMPLOYEE_ID as employeeID 
            , tblJH.JOB_ID as jobID 
            , tblJH.DEPARTMENT_ID as departmentID 
            , ROWNUM as rowNumber 
       
        from   HR.JOB_HISTORY tblJH 
 
        order by 
                tblJH.END_DATE DESC 
              , tblJH.START_DATE DESC
              , tblJH.EMPLOYEE_ID ASC
     
    ) tblA 
     
FETCH FIRST 3 ROWS ONLY

Output

 

 

Query – Use Analytic Function

The Analytic Function pathway needs an inner select and within that an inner select a computed column.

That computed column is filled out by the system.

And, it can order or rank based on partition ( grouping) or against entire dataset.


select *  
   
from 
   
    (  
          select  
              tblJH.END_DATE as dateEnded  
            , tblJH.START_DATE as dateStarted    
            , tblJH.EMPLOYEE_ID as employeeID  
            , tblJH.JOB_ID as jobID  
            , tblJH.DEPARTMENT_ID as departmentID  
            , row_number()  
                 over  
                  ( 
                       order by  
                            tblJH.END_DATE desc 
                          , tblJH.START_DATE desc 
                          , tblJH.EMPLOYEE_ID asc 
                  ) as seqNbr 
         
        from   HR.JOB_HISTORY tblJH  
   
        order by 
            tblJH.END_DATE DESC 
       
    ) tblA  
       
where seqNbr <= 3 

Output

 

Query – Use RowNum

The RowNum query is the one that is the easiest to get a bit sloppy over.

And, it seems to need two sub-selects.

The innermost query will have the Order By.

The middle will add the ranking.

And, the outermost will filter based on the ranking.

-- Use RowNum
select *
from   (
            select  
                  dateEnded
                , dateStarted
                , employeeID
                , jobID
                , departmentID
                , ROWNUM as seqNbr
            from 
            -- get ordered data
            ( 
                select  
                      tblJH.END_DATE as dateEnded 
                    , tblJH.START_DATE as dateStarted   
                    , tblJH.EMPLOYEE_ID as employeeID 
                    , tblJH.JOB_ID as jobID 
                    , tblJH.DEPARTMENT_ID as departmentID 
                from   HR.JOB_HISTORY tblJH 
                order by 
                      tblJH.END_DATE DESC 
                    , tblJH.START_DATE DESC
                    , tblJH.EMPLOYEE_ID ASC
            ) tblA
        ) tblB    
-- filter based on seqNbr     
where seqNbr <= 3
;


References

  1. Oracle-Base
    • Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
      Link
  2. Home / Database / Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration
    • Database SQL Reference
  3. Vertabelo
    • Row_Number
      • Bartłomiej Jańczak
        • Oracle ROWNUM Explained
          Link
  4. Stack Overflow
    • How do I limit the number of rows returned by an Oracle query after ordering?
      Link
    • How to use Oracle ORDER BY and ROWNUM correctly?
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s