Query to Get Last Version Of Table Data With Different Methods(KEEP, FIRST, DENSE_RANK, ROW_NUMBER)

A general programming practise to store old snapshot of data with versioning.
For instance currency table may be updated three or more times a day.
When application developers need currency info, first they have to get last version of curency rates for given day.
For future analysis, old currencies have to be stored, too.
So you have to write some queries to get last version of table data.
I demonstrate 3 methods to achieve situation:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 15 15:14:28 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table currency(code varchar2(3), day date, version number, price number );
Table created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 1, 1.435);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 2, 1.437);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 3, 1.441);
1 row created.
SQL> INSERT INTO currency VALUES ('EUR', SYSDATE, 1, 1.800);
1 row created.
SQL> INSERT INTO currency VALUES ('EUR', SYSDATE, 2, 1.798);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE + 1, 1, 1.444);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE + 1, 2, 1.443);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on
SQL> SELECT c.code, c2.DAY, c.price
2 FROM currency c
3 ,(SELECT code, trunc(DAY) DAY, MAX(version) version
4 FROM currency
5 GROUP BY code, trunc(DAY)) c2
6 WHERE c.code = c2.code
7 AND trunc(c.DAY) = c2.DAY
8 AND c.version = c2.version;
COD DAY PRICE
--- --------- ----------
USD 16-FEB-07 1.443
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441

Execution Plan
----------------------------------------------------------
Plan hash value: 3100612241
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 6 (34)| 00:00:01|
|* 1 | FILTER | | | | ||
| 2 | HASH GROUP BY | | 1 | 75 | 6 (34)| 00:00:01|
|* 3 | HASH JOIN | | 1 | 75 | 5 (20)| 00:00:01|
| 4 | TABLE ACCESS FULL| CURRENCY | 7 | 350 | 2 (0)| 00:00:01|
| 5 | TABLE ACCESS FULL| CURRENCY | 7 | 175 | 2 (0)| 00:00:01|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"."VERSION"=MAX("VERSION"))
3 - access("C"."CODE"="CODE" AND TRUNC(INTERNAL_FUNCTION("C"."DAY"))=
TRUNC(INTERNAL_FUNCTION("DAY")))
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
125 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> SELECT code
2 ,trunc(DAY) DAY
3 ,MIN(price) keep(dense_rank FIRST ORDER BY version DESC) price
4 FROM currency
5 GROUP BY code, trunc(DAY);
COD DAY PRICE
--- --------- ----------
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441
USD 16-FEB-07 1.443

Execution Plan
----------------------------------------------------------
Plan hash value: 3859059256
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 266 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 7 | 266 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CURRENCY | 7 | 266 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> SELECT code, DAY, price
2 FROM (SELECT code
3 ,trunc(DAY) DAY
4 ,price
5 ,row_number() over(PARTITION BY code, trunc(DAY) ORDER BY version DESC) rn
6 FROM currency)
7 WHERE rn = 1;
COD DAY PRICE
--- --------- ----------
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441
USD 16-FEB-07 1.443

Execution Plan
----------------------------------------------------------
Plan hash value: 2059110773
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 245 | 3 (34)| 00:00:01 |
|* 1 | VIEW | | 7 | 245 | 3 (34)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 7 | 266 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL | CURRENCY | 7 | 266 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY
"CODE",TRUNC(INTERNAL_FUNCTION("DAY")) ORDER BY INTERNAL_FUNCTION("VERSION")
DESC )<=1)
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>