Search code examples
sqloracle-databaseindexingrownum

How to use ROWNUM with nested queries

SELECT * FROM(
SELECT * FROM(
SELECT PART_NO, SRC_PART_NO, CTNM_ENG, DESCRIPTION, USER_ID, REG_DT, CHG_DT, FLAG,       
(select count(*) from ( SELECT PART_NO, SRC_PART_NO, CTNM_ENG, DESCRIPTION, USER_ID, REG_DT, CHG_DT, FLAG
FROM GM_PART_LIST
WHERE PART_NO LIKE  '%' || '%' AND SRC_PART_NO LIKE  '%' || '%' AND CTNM_ENG LIKE  'BOLT'|| '%'
AND 1 = 1) ) as total_count -- Nested subquery that return total count of record set. plug in same where conditions.
FROM GM_PART_LIST
WHERE PART_NO LIKE  '%' || '%' AND SRC_PART_NO LIKE  '%' || '%' AND CTNM_ENG LIKE  'BOLT'|| '%'
AND 1 = 1
ORDER BY PART_NO ASC))
WHERE ROWNUM BETWEEN 2 AND 202;

How is it that with the above query if I search between 1 and 200 It pulls records fine but when I switch it to 2 or another integer above 1 it fails to query any records? is this a syntax issue? thank you in advance for any help anyone can offer.

Solution

  • ROWNUM is assigned when a row is evaluated for the where conditions. The first row from the row source is retrieved and given ROWNUM=1. If one of the were conditions is ROWNUM > 1, this row will not be selected.

    Then ROWNUM=1 is reassigned to the next row (which again will fail the where clause) and so on. This is because in the end ROWNUM must run consecutively from 1, it will not be a sequence with gaps. So any condition that doesn't allow ROWNUM to be 1 (example: where mod(ROWNUM, 2) = 0) will produce zero rows, and for exactly the same reason.