CODEWITHSUNDEEP
sqloracle-databasesql-order-by
gavenkoa
gavenkoa

Reputation: 48893

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

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?

Upvotes: 3

Views: 1791

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

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.

Upvotes: 1

Related Questions