Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am executing the following query on two different database servers:

SELECT start_date,
  end_date,
  resort_id
FROM employee_activity
WHERE employee_id = 27
  AND start_date < to_date('2012-11-09', 'YYYY-MM-DD')
  AND is_overridden = 1
  AND ROWNUM = 1 
ORDER BY start_date DESC;

This query returns different records on Exadata than on the other server. On Exadata, it first selects the first row and then does the ordering. On the other server, it first does the ordering and then returns the first row.

We are using Oracle 11g on both servers.

Now the issue we are facing is that we have upgraded our database servers to Exadata and we have used such queries in a lot of places in our codebase. So can we overcome this issue?

share|improve this question
    
did you test the accepted answer? –  miracle173 Aug 28 at 17:57

2 Answers 2

up vote 3 down vote accepted

The value for rownum is assigned before the order by, so you need to wrap this into a derived table:

select start_date,
       end_date,
       resort_id
from (
    SELECT start_date,
           end_date,
           resort_id,
           rownum as rn
    FROM employee_activity
    WHERE employee_id = 27 
      AND start_date < to_date('2012-11-09', 'YYYY-MM-DD') 
      AND is_overridden = 1
    ORDER BY start_date DESC
)
WHERE rn = 1

For more details on the rownum behaviour, please see the manual: ROWNUM pseudocolumn

share|improve this answer
    
But how it worked since long on previous servers with same database ? –  Ram Dutt Shukla Feb 22 '13 at 7:38
2  
@RamDuttShukla: that was pure conincidence. –  a_horse_with_no_name Feb 22 '13 at 7:44
1  
Most likely the order of the rows in the tablespace blocks (rowid) determined it –  Phil Feb 22 '13 at 11:34
    
Your link stopped working and I replaced it. The new link does look like the new location for the same page but I'm not sure. Please take a look. –  Andriy M Aug 27 at 8:32
    
"The value for rownum is assigned before the order by, so" there is the same problem as in the OP. This was observed by dba.stackexchange.com/a/112363/2047 –  miracle173 Aug 28 at 17:56

This answer is wrong, because the value for rownum is assigned before the order by is executed.

Below is the correct one,

SELECT start_date,  
       end_date,  
       resort_id,  
       ROWNUM  
FROM   (SELECT   start_date,  
                end_date,  
                resort_id  
       FROM     employee_activity  
       WHERE    employee_id = 27  
       AND      start_date < TO_DATE ('2012-11-09', 'YYYY-MM-DD')  
       AND      is_overridden = 1  
       ORDER BY start_date DESC
      )  
WHERE  ROWNUM = 1
share|improve this answer
    
+1 You are right, congratulations. –  miracle173 Aug 28 at 17:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.