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.
