Re: order by not working in view ?

On Thursday, April 9, 2020, David Gauthier <davegauthierpg(at)gmail(dot)com> wrote:

> psql (9.6.7, server 11.3) on linux
>
> In the copy/paste below, first 2 lines returned by a select on the view,
> why didn't it sort on start_datetime correctly ? I would think that the
> one started on 04-08 would come before the one on 04-09 ?
> [...]
> dvdb=> create or replace view spview as (select
> sj_id,sim_phase_name,status,start_datetime,end_datetime,duration_hrs from
> dvm.sim_phases order by sj_id,start_datetime);
> CREATE VIEW
> dvdb=> select * from spview where sj_id in (select sj_id from sjview where
> dvm_id = 1102);
>

You should be including Explain output when posting questions like this.

Just because the from clause relation is ordered does not mean the final
result will be. In this case the system fetched rows from the ordered view
out of order during fulfillment of the where expression (this may not be
true implementation but it is seemingly what happened). The optimizations
the planner is allowed to make are not constrained by order by).

In short, adding order by to views is misleading to the user unless the
user only writes (select * from viewname;) Queries that export data and
want ordering need to specify it themselves.

David J.