Home

Description

The rule "ROWNUM should not be used at the same query level as ORDER BY" states that the ROWNUM pseudo-column should not be used in the same query as the ORDER BY clause. This is because the ROWNUM pseudo-column is assigned to each row before the ORDER BY clause is applied, so the results of the query may not be consistent. Therefore, it is recommended to use the ORDER BY clause before the ROWNUM pseudo-column is applied.

Key Benefits

• Performance: ROWNUM should not be used at the same query level as ORDER BY as it can slow down the query performance.
• Accuracy: ROWNUM does not guarantee the accuracy of the results as it does not take into account the ORDER BY clause.
• Unpredictability: ROWNUM can be unpredictable as it does not guarantee the order of the results.

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)