Select first result only
The ROWNUM filter applies before the sorting. What you need to do is this:
SELECT * FROM ( SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO ORDER BY TOTALS.TOTAL DESC)WHERE ROWNUM <= 1
Wrap the rownum where in another external query:
select * from (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL FROM PURCHASE GROUP BY CLIENTNO) TOTALS WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO ORDER BY TOTALS.TOTAL DESC)where ROWNUM <= 1;
For the MySQL people that find this, use ROW_NUMBER() to get the same result with:
WITH ranked AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY CLIENTNO ORDER BY TOTALS.TOTALS ) ROWNUM, CLIENT.CLIENTNO, . . )SELECT * FROM ranked WHERE ROWNUM <=1Some helpful info: https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function and https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number