René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

RANK [Oracle SQL analytic function]

RANK is an analytic function that can be used to get the rank of a row in respect to a group of rows. This little example will demonstrate this. First you have to create and load a table that contains each month's average temprature in Edinburgh in the years 1764-1820. The script to do that can be found here.
After filling this table, RANK can be used to query the hottest month in each year: 
set feedback off
set pages 50000

select month,year,avg_temp from 
  (select rank() over (partition by year order by avg_temp desc) r, avg_temp, month, year from scottish_weather)
where r=1;
This returns: 
     MONTH       YEAR   AVG_TEMP
---------- ---------- ----------
         7       1764       59,9
         7       1765       58,5
         8       1766       59,5
         8       1767       59,8
         8       1768       58,7
         7       1769       60,1
         8       1770       58,2
         7       1771       57,4
         7       1772         58
         8       1773       58,3
         7       1774       56,8
         7       1775       59,7
         7       1776       59,6
         8       1777       59,2
         7       1778       61,2
         7       1779       65,2
         8       1780       63,2
         7       1781       60,4
         7       1782       60,1
         7       1783       63,2
         7       1784       58,5
         6       1785       60,7
         8       1786       58,7
         7       1787         60
         8       1787         60
         7       1788       60,3
         8       1789       61,6
         7       1790         59
         7       1791       58,6
         8       1792       60,3
         7       1793         60
         7       1794       60,7
         8       1795       59,3
         8       1796       59,5
         7       1797       60,9
         6       1798       60,8
         7       1799         58
         7       1800       61,6
         8       1801       60,4
         8       1802       60,1
         7       1803       62,8
         6       1804       59,6
         8       1805       59,4
         8       1806       58,8
         7       1807         61
         7       1808       62,5
         8       1809       57,4
         8       1810         58
         7       1811       59,3
         8       1812       57,2
         7       1813       59,3
         7       1814       59,4
         7       1815       58,2
         7       1816       55,7
         7       1817       57,2
         7       1818         60
         8       1819       62,7
         7       1820         59
Note: two rows are returned for the year 1787 because the hottest average temperature are the same for July and August.

Using Rank to select a month's last record

create table test_month (
  val       number,
  dt        date
);

alter session set nls_date_format = 'DD.MM.YYYY';

insert into test_month (val,dt) values (18,'28.08.2000');
insert into test_month (val,dt) values (19,'02.08.2000');
insert into test_month (val,dt) values (22,'27.09.2000');
insert into test_month (val,dt) values (23,'04.09.2000');
insert into test_month (val,dt) values (20,'12.08.2000');
insert into test_month (val,dt) values (24,'15.09.2000');
insert into test_month (val,dt) values (19,'27.07.2000');
insert into test_month (val,dt) values (18,'01.07.2000');
insert into test_month (val,dt) values (21,'26.07.2000');
insert into test_month (val,dt) values (24,'03.06.2000');
insert into test_month (val,dt) values (22,'11.07.2000');
insert into test_month (val,dt) values (21,'14.06.2000');

select val,dt from (select
   val,dt,rank() over(partition by to_char(dt,'YYYY.MM') order by dt desc) rn 
from test_month
 )
where rn = 1;

       VAL DT
---------- ----------
        21 14.06.2000
        19 27.07.2000
        18 28.08.2000
        22 27.09.2000

Links

See also