On Thursday, April 9, 2020, David Gauthier <
davegauthierpg@gmail.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.