Home

ROWNUM should not be used at the same query level as ORDER BY

Rule description

  • ROWNUM should not be used at the same query level as ORDER BY

 

Non-compliant Code Example

SELECT
    NAME,
    ADDRESS,
    CREDIT_LIMIT
FROM
    CUSTOMERS
WHERE ROWNUM <= 5  --Non compliant code (ROWNUM is used at the same query level as ORDER BY)
ORDER BY CREDIT_LIMIT;

Compliant Code Example

SELECT * FROM
	(	SELECT
		NAME,
		ADDRESS,
		CREDIT_LIMIT
		FROM CUSTOMERS
		ORDER BY CREDIT_LIMIT
	)
WHERE ROWNUM <= 5;  --Compliant code (ROWNUM is used at the same query level as ORDER BY)
Visual Expert 2020
 VEPLSQLRULE49