Oracle: how to get the max and the preceding values?
May
2
Written by:
5/2/2014 2:05 PM
There could be several ways to do it. For example, I've already seen some which were very slow, creating temporary tables and indexes, and making join between the records by comparing the max value... Of course, the goal of this post is to show the fatest way to do it.
Analytic Functions, which have been available since Oracle 8.1.6, are designed to address such problem, and other ones like "Top-N queries", "Subtotals by group", "Last values in a historic". Most of these problems can be solved using standard PL/SQL using imbricated queries, however the performance is often not what it should be. Analytic Functions add extensions to the SQL language that not only make these operations easier to code; they make them faster than could be achieved with pure SQL or PL/SQL.
Considering our problem, the following Oracle keywords have to be used:
- MAX ... OVER(PARTITION BY... ORDER BY), in order to get the max value for each group
- LEAD(...,N) OVER(PARTITION BY... ORDER BY...), in order the get the Nth precedent value. Depending on the order done in the ORDER BY, the LAG function could be used too.
- RANK(), in order to get the value that hits the MAX value. The RANK value is in this case equal to 1.
Consider the following PL/SQL script:
create table table_test(field0 number(2), field1 number(2));
insert into table_test values (0,0);
insert into table_test values (1,0);
insert into table_test values (1,1);
insert into table_test values (2,0);
insert into table_test values (2,1);
insert into table_test values (2,2);
commit;
select field0, field1, prev_field1 from (
select field0,max(field1) over(partition by field0 order by field0,field1 desc) as field1, lead(field1,1) over(partition by field0 order by field0,field1 desc) as prev_field1, rank() over(partition by field0 order by field0,field1 desc) as rn from table_test
) where rn=1;
This will give the expected values:
FIELD0 FIELD1 PREV_FIELD1
---------- ---------- -----------
0 0
1 1 0
2 2 1
For example, it could be used in order to purge some tables which are using a version field to make some audit. If there's no hole in the version suite, it is bettter to use a decode(version-1,-1,0,version-1) syntax. If some versions are missing, the above script makes sense.
Using it in the financial software Sophis database to make some purge in the AUDIT_MVT table, it can be used as follow:
select refcon, version, prev_version from (
select refcon,max(version) over(partition by refcon order by refcon,version desc) as version, lead(version,1) over(partition by refcon order by refcon,version desc) as prev_version, rank() over(partition by refcon order by refcon,version desc) as rn from audit_mvt
) where rn=1;