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

Share

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:

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

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

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.

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:

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.

2 Comments
  • 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:

    hi
    please add the date in mm/yy format on project table and wants to get data for monthly max and low for all years ,
    like
    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.

Close