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:

   ID	    PROJECT_CODE    COST    CREATE_DATE
-----	    ------------    ----    -----------
    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:

	PROJECT_CODE	MAX_RECENT
	------------	----------
	       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):

MIN(cost) KEEP(DENSE_RANK LAST ORDER BY create_date)
MAX(cost) KEEP(DENSE_RANK FIRST ORDER BY create_date)
MIN(cost) KEEP(DENSE_RANK FIRST ORDER BY create_date)

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

Conclusion

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.

References

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.