0

I have this query :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total 
from budget where as_of_dt < to_date('2016-06-28', 'yyyy-MM-dd') 
order by as_of_dt desc;

Which returns bunch of numbers such as :

enter image description here

My requirement is to always capture the first number, so when I try to do that :

select * from (
  select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total 
  from budget where as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd') 
  order by as_of_dt desc
) WHERE ROWNUM = 1

I get this result :

enter image description here

Which is why I m puzzled about this, why is that I m getting the second row instead of the first one? What am I doing wrong here?

Update including as_of_dt in result for first query:

enter image description here

  • Can you show us the output of the 1st query, including the as_of_dt column? They two rows may have the same exact date. – ypercubeᵀᴹ Jun 13 '16 at 16:50
  • @ypercubeᵀᴹ please see my update – Gandalf StormCrow Jun 13 '16 at 16:54
  • 1
    May want to to_char the as_of_dt column to see the time? – Nick S Jun 13 '16 at 16:59
  • 1
    Something else that doesn't look quite right, you have < to_date('2016-05-16', 'yyyy-MM-dd') but are getting values greater than that date? – Nick S Jun 13 '16 at 17:02
  • @NickS nice catch! (although we are not yet sure if all the dates are June-2016 or June 16th ;) – ypercubeᵀᴹ Jun 13 '16 at 17:05
5

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them):

The order by has ties. There is not one "first row" but 3 of them.

So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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