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

Question

Test data:

create table test (
  grp varchar2(16)
, mbr varchar2(16)
, reading1 number
, reading2 number
);

-- 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
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'C', 'r', '100.1', '200.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 ) 
    values ( 'C', 's', '100.2', '200.4' ) ;
end;
/

See dbfiddle.

select * from test;
GRP  MBR  READING1  READING2  
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...

Answer 1

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, 
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      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,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2,
  rank() over (partition by grp order by mbr desc) as rank_
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
) where rank_ = 1
;