This one is better algorithm
sELECT PNLGRPNAME,
RECNAME ,rCount,RANK1,PORTAL_URltext, DESCR254 FROM (
SELECT PNLGRPNAME,
RECNAME ,rCount,
RANK() OVER (partition BY RECNAME ORDER BY RECNAME,rCount DESC)AS RANK1 FROM
(SELECT A.PNLGRPNAME,
B.RECNAME ,
COUNT(1) AS rCount
FROM PSPNLGROUP A,
PSPNLFIELD B
WHERE ( A.PNLNAME = B.PNLNAME )
GROUP BY A.PNLGRPNAME,
B.RECNAME
) ) R, PSPRSMDEFN D
WHERE recname ='CA_DETAIL'
AND R.PNLGRPNAME= D.PORTAL_URI_SEG2
AND RANK1 <4 AND PORTAL_NAME='EMPLOYEE' AND PORTAL_URltext NOT LIKE '%?%' ORDER BY RANK1