Does partition by / order by imply ordering in a query?

user_173149 0
Viewed 726

I see no difference in:

select
  ID,
  TYPE,
  XTIME,
  first_value(XTIME) over (partition by TYPE order by XTIME)
from SERIES;

and:

select
  ID,
  TYPE,
  XTIME,
  first_value(XTIME) over (partition by TYPE order by XTIME)
from SERIES
order by TYPE, XTIME;

Does partition by / order by imply ordering in a query?

sql-order-by oracle sql
2 Answers

No it does not.

The result set from a query is ordered only when it has an explicit order by clause. Otherwise the result set is in an arbitrary order. Often, the underlying algorithms used for running the query determine the ordering of the result set.

You can only depend on the final ordering when you have an order by clause for the outermost select. That the result sets are in the same order is simply a coincidence.

I am curious why you don't simply use this?

min(xtime) over (partition by type)
user_11440350

The difference between the two queries is that the second one would be ordered ascending by TYPE and then XTIME, while the first one would, in theory, have random ordering.

The PARTITION BY and ORDER BY clause used in the call to FIRST_VALUE have an effect on that particular analytic function, but do not by themselves affect the final order of the result set.

In general, if you want to order a result set in SQL, you need to use an ORDER BY clause. If both your queries happen to have the same ordering right now, it is only by chance, and is not guaranteed. How might this happen? Well, if Oracle is iterating the clustered index of your table, and that ordering happens to coincide with ORDER BY TYPE, XTIME, then you would see this order even without using ORDER BY.

user_18632290
Related