# Oracle analytic functions - how to get the highest RANKing row?

Test data:

``````create table test (
grp varchar2(16)
, mbr varchar2(16)
);

-- group A: 3 members, 1 duplicate set
-- group B: 2 members, 1 duplicate, one reading NULL
-- group C: 2 members, no repeats, no NULLs
begin
values ( 'A', 'x', '1.0', '2.0' ) ;
values ( 'A', 'y', '1.1', '2.2' ) ;
values ( 'A', 'z', '1.2', '2.4' ) ;
values ( 'A', 'x', '1.0', '2.0' ) ;
values ( 'A', 'y', '1.1', '2.2' ) ;
values ( 'A', 'z', '1.2', '2.4' ) ;
values ( 'B', 'y', '20.2', null ) ;
values ( 'B', 'x', '20.4', '40.4' ) ;
values ( 'B', 'y', '20.2', null ) ;
values ( 'B', 'x', '20.4', '40.4' ) ;
values ( 'C', 'r', '100.1', '200.2' ) ;
values ( 'C', 's', '100.2', '200.4' ) ;
end;
/
``````

See dbfiddle.

``````select * from test;
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
B    y    20.2      NULL
B    x    20.4      40.4
B    y    20.2      NULL
B    x    20.4      40.4
C    r    100.1     200.2
C    s    100.2     200.4
``````

Problem:

Write a query that does all of the following:

{1} Find unique rows.

{2} Find the last 2 members (mbr) of each group (grp). Assumption: when the members are put into alphabetical order, the last member is the one with the last letter (eg if we have 'x','y','z', the last letter is 'z'). Do not hard-code the letter into the query.

{3} Perform the following calculation: when rows are grouped (according to their grp letter), for each row containing the last letter: reading1 - preceding reading2 ( ie reading2 of the row containing the letter 'y' ), and reading2 - preceding reading1. Treat NULLs as 0.

Using our sample/test data:

``````-- {1}
GRP  MBR  R1     R2
A    x    1      2
A    y    1.1    2.2
A    z    1.2    2.4
B    x    20.4   40.4
B    y    20.2   0
C    r    100.1  200.2
C    s    100.2  200.4

-- {2}
GRP  MBR  RESULT1  RESULT2  RANK_
A    x    1        2        1
A    y    -0.9     1.2      2
A    z    -1       1.3      3
B    x    18       39.2     1
B    y    -20.2    -20.4    2
C    r    100.1    180      1
C    s    -100     100.3    2

-- {3} required/final result
grp  result1  result2
A      -1.0      1.3    -- (result1: 1.2-2.2)     (result2: 2.4-1.1)
B     -20.2    -20.4    -- (result1: 20.2-40.4)   (result2: 0-20.4)
C    -100.0    100.3    -- (result1: 100.2-200.2) (result2: 200.4-100.3)
``````

Existing code:

This query returns result set {2}.

``````-- {2}
select
grp
, mbr
, r1 - lag( r2, 1, 0 ) over ( order by grp ) as result1
, r2 - lag( r1, 1, 0 ) over ( order by grp ) as result2
, rank() over ( partition by grp order by mbr ) as rank_
from
(
select distinct
grp
, mbr
, nvl( reading1, 0 ) r1
, nvl( reading2, 0 ) r2
from test
order by grp, mbr
) ;
``````

Question: How can we get to resultset {3}, without using hard-coded values (such as rank_ = 2 in a WHERE clause)? Not sure whether RANK() is needed (for the final query) at all...

I fail to see the point of a requirements such as avoiding `WHERE rank_ =`, but here it is, without `RANK()`, or hardcoding a constant (still, hardcoding is done by using `FIRST_VALUE`):

``````select distinct grp,
first_value(result1) over (partition by grp order by mbr desc) as result1,
first_value(result2) over (partition by grp order by mbr desc) as result2
from (
select
grp, mbr,
from (select unique grp, mbr,
from test)
);

GRP                 RESULT1    RESULT2
---------------- ---------- ----------
A                        -1        1.3
B                     -20.2      -20.4
C                      -100      100.3
``````

When this is easier to read in my opinion:

``````select grp, result1, result2 from (
select
grp,