org.sonar.l10n.plsqlopen.rules.plsql.SelectWithRownumAndOrderBy.html Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of zpa-checks Show documentation

Z PL/SQL Analyzer :: Checks

The newest version!


In a SELECT expression, the WHERE clause is executed before the ORDER BY clause. Consider this example:


DECLARE
  CURSOR recent_users IS
    SELECT user.name,
           user.creation_date
      FROM user
     WHERE ROWNUM <= 5
     ORDER BY user.creation_date DESC;
BEGIN
  ...



This query won't return the last 5 created users. The database will filter the users without any ordering and after, apply the ORDER BY clause. The corrected query is:


DECLARE
  CURSOR recent_users IS
    SELECT name,
           creation_date
      FROM (SELECT user.name,
                   user.creation_date
              FROM user
             ORDER BY user.creation_date DESC)
     WHERE ROWNUM <= 5;
BEGIN
  ...