Oracle SQL Tip – How to rank a data set and work with its top-ranked or bottom-ranked rows


In this article we will take a brief look at the Oracle SQL FIRST/LAST aggregate functions that allow you to select a value from the first or last row(s) of a sorted group, when the needed value is not the sort key.

For example consider a table “PROJECT_COST” having the following structure and data:

-----	    ------------    ----    -----------
    1       PRJ01	     100     30.08.2009
    2	    PRJ01	      80     30.08.2009
    3	    PRJ01	     120     05.07.2009
    4	    PRJ02	      50     08.05.2009
    5	    PRJ02	      30     25.06.2009

My question is “how to select for each project the maximum cost value among the most recent values”. This is the desired output:

	------------	----------
	       PRJ01	       100
	       PRJ02	        30

I found a simple and efficient solution, using oracle FIRST aggregate function:

SELECT   project_code,
         MAX (COST)KEEP (DENSE_RANK LAST ORDER BY create_date) "max_recent"
    FROM project_cost
GROUP BY project_code;

Other possible usages (for similar questions):


This technique becomes even more useful when joining data from many tables. The SQL statement will remain simple, easy to understand.


The FIRST/LAST aggregate functions allow you to rank a data set and work with its top-ranked or bottom-ranked rows. After finding the top or bottom ranked rows, an aggregate function is applied to any desired column. That is, FIRST/LAST lets you rank on column A but return the result of an aggregate applied on the first-ranked or last-ranked rows of column B.
These functions have the following syntax:

aggregate_function KEEP ( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]

Note that the ORDER BY clause can take multiple expressions.


Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

2 thoughts on “Oracle SQL Tip – How to rank a data set and work with its top-ranked or bottom-ranked rows”
  • Ciprian Radu says:

    I just want to add an observation. Besides DENSE_RANK, Oracle also has the RANK function. The difference between DENSE_RANK and RANK is that, when using RANK and multiple rows share the same rank, the next rank in the sequence is not consecutive. Example (notice rank 4):

    NAME VALUE rank
    ONE 100 1
    TWO 200 2
    THREE 200 2
    FOUR 300 4

    DENSE_RANK works like RANK, but gives consecutive ranks. So, for the above example we will have (notice rank 3):

    NAME VALUE rank
    ONE 100 1
    TWO 200 2
    THREE 200 2
    FOUR 300 3

    September 8, 2009 at 8:13 am
  • n says:

    please add the date in mm/yy format on project table and wants to get data for monthly max and low for all years ,
    date [roject maxVAL min proj min VAL
    01/10 proj1 1000 proj2 500
    02/10 proj1 5000 proj4 100

    September 25, 2010 at 8:45 am

Comments are closed.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.