1

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?

0

1 Answer 1

4

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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