CODEWITHSUNDEEP
sqloracle-database
Naraharisetti ravi teja
Naraharisetti ravi teja

Reputation: 1

Fetching data for rownum = 1 but not for other than 1 in Oracle SQL

Getting result (rownum = 1):

SELECT rownum,
       A.loadplan_name
FROM   (SELECT loadplan_name,
               run_date
        FROM   dcbp13_bia_odirepo.citizen_odi_required_loadplans
        WHERE  run_date LIKE '13-FEB-19'
        ORDER  BY run_date DESC,
                  loadplan_name ASC) A
WHERE  rownum = 1

Not getting result (rownum ≠ 1):

SELECT rownum,
       A.loadplan_name
FROM   (SELECT loadplan_name,
               run_date
        FROM   dcbp13_bia_odirepo.citizen_odi_required_loadplans
        WHERE  run_date LIKE '13-FEB-19'
        ORDER  BY run_date DESC,
                  loadplan_name ASC) A
WHERE  rownum = 2

Upvotes: 0

Views: 368

Answers (2)

Gauravsa
Gauravsa

Reputation: 6514

You are quite close. As doco says, operations for ROWNUM values greater than a positive integer are always false. You can use less than, equal to instead of greater than. So, this query is ok:

SELECT rownum,
       A.loadplan_name
FROM   (SELECT loadplan_name,
               run_date
        FROM   dcbp13_bia_odirepo.citizen_odi_required_loadplans
        WHERE  run_date LIKE '13-FEB-19'
        ORDER  BY run_date DESC,
                  loadplan_name ASC) A
WHERE  rownum < 2;

SELECT rownum,
       A.loadplan_name
FROM   (SELECT loadplan_name,
               run_date
        FROM   dcbp13_bia_odirepo.citizen_odi_required_loadplans
        WHERE  run_date LIKE '13-FEB-19'
        ORDER  BY run_date DESC,
                  loadplan_name ASC) A
WHERE  rownum = 2;

but not this:

SELECT rownum,
       A.loadplan_name
FROM   (SELECT loadplan_name,
               run_date
        FROM   dcbp13_bia_odirepo.citizen_odi_required_loadplans
        WHERE  run_date LIKE '13-FEB-19'
        ORDER  BY run_date DESC,
                  loadplan_name ASC) A
WHERE  rownum > 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is explained in the documentation. rownum is calculated when the result set is returned. So, it increments only as a new rows is put into the result set.

Hence, it never takes on the value "2" without already having the value "1".

As explained:

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
FROM employees
WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

The solution is typically something like this:

SELECT rn, A.LOADPLAN_NAME
FROM (SELECT LOADPLAN_NAME, RUN_DATE, rownum as rn
      FROM DCBP13_BIA_ODIREPO.CITIZEN_ODI_REQUIRED_LOADPLANS      
      WHERE RUN_DATE like '13-FEB-19'
      ORDER BY RUN_DATE DESC, LOADPLAN_NAME ASC
     ) A  
WHERE rn = 2;

Upvotes: 3

Related Questions