plsql - How can i write a query in PL/SQL to find 3 maximum balance from Account table using cursor?

Question

DECLARE
     CUSTID NUMBER; ANO NUMBER; BALANC NUMBER; TYP ACCOUNT.TYPE%TYPE; STATU ACCOUNT.STATUS%TYPE;
     CURSOR S IS SELECT *  FROM ACCOUNT WHERE STATUS = 'active';
BEGIN
    OPEN S;
     FOR A IN 1..3 LOOP
        FETCH S 
        DBMS_OUTPUT.PUT_LINE('CUST ID : '||CUSTID||'   NO:'||ANO || '  TYPE :' || TYP || '   STATUS :' || STATU); 
     END LOOP;
CLOSE S;
END;

i'm trying to find 3 maximum balance on ACCOUNT table but it doesn't work !

Answer 1

You don't need PL/SQL or a cursor for this:

SELECT * 
FROM 
  SELECT a.*,
         dense_rank() over (order by balance desc) as rnk
  FROM account 
  WHERE status = 'active' 
) t
WHERE rnk <= 3;