TABLE

CREATE TABLE find_rank(group_id VARCHAR2(2),val NUMBER);

INSERT INTO find_rank VALUES ('G1',11);

INSERT INTO find_rank VALUES ('G1',12);

INSERT INTO find_rank VALUES ('G1',13);

INSERT INTO find_rank VALUES ('G1',14);

INSERT INTO find_rank VALUES ('G2',8);

INSERT INTO find_rank VALUES ('G2',10);

INSERT INTO find_rank VALUES ('G2',10);

INSERT INTO find_rank VALUES ('G2',19);

INSERT INTO find_rank VALUES ('G2',21);

INSERT INTO find_rank VALUES ('G3',1);

INSERT INTO find_rank VALUES ('G3',2);

INSERT INTO find_rank VALUES ('G3',4);

INSERT INTO find_rank VALUES ('G4',0);

INSERT INTO find_rank VALUES ('G5',-1);

INSERT INTO find_rank VALUES ('G5',-2);

INSERT INTO find_rank VALUES ('G5',-3);

COMMIT;

Answer

SELECT DISTINCT * FROM

(SELECT group_id,val,

dense_rank() over

(PARTITION BY group_id ORDER BY val DESC) rn

FROM find_rank) t

WHERE t.rn=&rank

with &rank = 2

GROUP_ID VAL RN
———————-
G1 13 2
G2 19 2
G3 2 2
G5 -2 2

If we need to have G4 also in the output even though it does not have a second/third highest value then

SELECT DISTINCT f.group_id,

CASE WHEN o.val > 0 THEN to_char(o.val) ELSE 'N/A' END val

FROM find_rank f

LEFT OUTER JOIN

(SELECT  DISTINCT group_id,val FROM

(SELECT group_id,val,

dense_rank() over (PARTITION BY group_id ORDER BY val DESC) rn

FROM find_rank) t

WHERE t.rn=&rank)o

ON f.group_id=o.group_id

with &rank =3

GROUP_ID VAL
——————-
G1 12
G2 10
G3 1
G4 N/A
G5 -3