Avoid a sub query to get a maximum number

I wrote the below query which I am trying to get the maximum transaction number of a code and status, is there a way I can re-write avoiding the sub-select?

SELECT CUST_NAME, TRANSCANTIONS_NB
FROM TRANSCANTIONS 
WHERE CODE = '477' 
AND STATUS IN ( '1' , '7' ) 
AND TRANSCANTIONS_NB = ( 
    SELECT MAX(TRANSCANTIONS_NB)
    FROM TRANSCANTIONS
    WHERE CODE ='477' AND STATUS IN ( '1' , '7' ) 
)

I am looking for a better performing solution which works in both Oracle and SQL Server.

Solution

You could use a TOP query here:

SELECT TOP 1 WITH TIES CUST_NAME, TRANSACTIONS_NB
FROM TRANSCANTIONS
WHERE CODE = '477' AND STATUS IN ('1', '7')
ORDER BY TRANSCANTIONS_NB DESC;

Using TOP 1 WITH TIES rather than just TOP 1 will ensure that multiple records will be returned in case there are more than one record having the max TRANSCANTIONS_NB value.

While Oracle does support WITH TIES in its FETCH FIRST clause, this is only available from 12+, and on top of that, we have to specify a number of tied records to retrieve. Therefore, on Oracle, a more canonical way to do this would use RANK() (or even ROW_NUMBER()), which also means we need a subquery:

WITH cte AS (
    SELECT CUST_NAME, TRANSACTIONS_NB,
           RANK() OVER (ORDER BY TRANSCANTIONS_NB DESC) rnk
    FROM TRANSCANTIONS
    WHERE CODE = '477' AND STATUS IN ('1', '7')
)

SELECT CUST_NAME, TRANSACTIONS_NB
FROM cte
WHERE rnk = 1;