Selecting only the highest value in Oracle SQL

Question

I have a query where I might get only one or possibly multiple records for each CONTROLNBR + LINENBR combo -

SELECT ACCTNBR, BOOKDATE, CONTROLNBR, LINENBR, TRANDETSEQ, 
ING1, ING1AMT, ING2, ING2AMT, ING3, ING3AMT, ING4, ING4AMT  
FROM TABLE1  WHERE COSTCENTER = '9999' ORDER BY CONTROLNBR, LINENBR, 
TRANDETSEQ

Some data returned might be -

12345   01-FEB-19   3771    1   5   R4  13  Y1  41  Y3  1
12345   01-FEB-19   3771    1   11  R4  21  Y1  41  Y3  9
12345   01-FEB-19   3771    1   12  R4  21  Y1  41  Y3  17
12345   01-FEB-19   3771    1   18  R4  21  Y1  61  Y3  45
12345   01-FEB-19   3772    3   5   R2  3   Y3  38  L1  8
12345   01-FEB-19   3773    1   5   N1  8   W1  2   L1  42
12345   01-FEB-19   3773    1   12  N1  10  W1  4   L1  45
24568   01-FEB-19   3786    2   4   L1 28   G2  40  N1  13
24568   01-FEB-19   3786    2   8   L1 28   G2  45  N1  18
24568   01-FEB-19   3786    2   12  L1 28   G2  48  N1  18

In each case, I would just need the line with the highest TRANDETSEQ number for each CONTROLNBR + LINENBR combo. So I would only want -

12345   01-FEB-19   3771    1   18  R4  21  Y1  61  Y3  45
12345   01-FEB-19   3772    3   5   R2  3   Y3  38  L1  8
12345   01-FEB-19   3773    1   12  N1  10  W1  4   L1  45
24568   01-FEB-19   3786    2   12  L1 28   G2  48  N1  18

Is there some function or something like LAST or MAX that might work here?

score 2 · Accepted Answer · answered Feb 22 '22 at 21:23

Rank them, and then fetch row(s) that rank as the highest.

Sample data:

SQL> with test (acctnbr, controlnbr, linenbr, trandatseq) as
  2    (select 12345, 3771, 1, 5 from dual union all
  3     select 12345, 3771, 1, 11 from dual union all
  4     select 12345, 3771, 1, 12 from dual union all
  5     select 12345, 3771, 1, 18 from dual union all
  6     --
  7     select 12345, 3772, 3, 5  from dual union all
  8     --
  9     select 12345, 3773, 1, 5  from dual union all
 10     select 12345, 3773, 1, 12 from dual union all
 11     --
 12     select 24568, 3786, 2, 4  from dual union all
 13     select 24568, 3786, 2, 12 from dual
 14    ),

Query begins here:

 15  temp as
 16    -- sort them
 17    (select t.*,
 18        rank() over (partition by acctnbr, controlnbr , linenbr order by trandatseq desc) rnk
 19     from test t
 20    )
 21  -- finally, fetch rows that rank as "highest"
 22  select acctnbr, controlnbr, linenbr, trandatseq
 23  from temp
 24  where rnk = 1;

   ACCTNBR CONTROLNBR    LINENBR TRANDATSEQ
---------- ---------- ---------- ----------
     12345       3771          1         18
     12345       3772          3          5
     12345       3773          1         12
     24568       3786          2         12

SQL>

Thank you - been away since I initially posted this, and finally got a chance to look at your answers. This, combined with the links someone else posted, finally got me to what I needed. I verified the data by searching for several multi-record orders without the rank, then searching for those same orders using the rank and the results were exactly what I needed! — BigRedEO, Feb 24 '22 at 19:30

Selecting only the highest value in Oracle SQL

1 Answers1