oracle11g - Mystery of rownum in oracle

Question

I am struggling to fetch the data based on rownum. When I execute the below query to get the results based rownum 1 to 4 then it is working fine.

  SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI WHERE ROWNUM BETWEEN 1 AND 4;

But when I am executing same query to get result from rownum 2 to 4 then it is not working, it doesn't return anything.

  SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI WHERE ROWNUM BETWEEN 2 AND 4;

As a workaround, when I use one more SELECT statement then it is working fine, but I don't think it is good approach to use SELECT multiple times only for rownum.

SELECT * FROM (SELECT ROWNUM TOTAL,MI.* FROM (SELECT USER_ID,CUSTOMER_NAME FROM ELEC_AUTO_MERC 
  ORDER BY CREATION_DATE DESC ) MI) WHERE TOTAL BETWEEN 2 AND 4;

Can you please help me out to create optimize query?

Answer 1

ROWNUM is weird in that it can be evaluated as part of a condition in the query - but if the row then fails to pass that filter, the ROWNUM value that it was assigned becomes available to be used again for the next row.

One important effect of this is that if you use any condition that excludes a ROWNUM value of 1, you will never get a match. The first row to be tested against this condition will be row 1; but then it will fail the test, so the next row will then be considered row 1; and so on.

So your condition ROWNUM BETWEEN 2 AND 4 can never be true.

The workaround you have found is the traditional one. Another would be to use an analytic function to rank the rows, then filter on the rank, e.g.:

SELECT MI.* FROM (
  SELECT USER_ID,CUSTOMER_NAME, RANK() OVER (ORDER BY CREATION_DATE DESC) AS the_rank
  FROM ELEC_AUTO_MERC 
  ) MI
WHERE the_rank BETWEEN 2 AND 4;

Several analytic functions - RANK, DENSE_RANK, and ROW_NUMBER - can be used for this purpose, and will produce slightly different results, especially if there are ties. Check out the docs.