RANK is an Analytical 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 Top N Query that shows how to perform a top n query with rank.
