order by not working in view ?

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: order by not working in view ?
Date: 2020-04-09 14:33:26
Message-ID: CAMBRECCk-JuysUnYTnNZwS__rOYbicQ3_6yqXRKKPO22doCOqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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=> \d sim_phases;
Table "dvm.sim_phases"
Column | Type | Modifiers
----------------+--------------------------+-----------
sj_id | integer |
sim_phase_name | character varying |
status | character varying |
error_message | character varying |
start_datetime | timestamp with time zone |
end_datetime | timestamp with time zone |
duration_hrs | numeric(5,2) |
Check constraints:
"sim_phases_name_check" CHECK (sim_phase_name::text = ANY
(ARRAY['presim'::character varying::text, 'runsim'::character
varying::text, 'postsim'::character varying::text, NULL::character
varying::text]))
"sim_phases_status_check" CHECK (status::text = ANY
(ARRAY['in_progress'::character varying, 'completed'::character varying,
'passed'::character varying, 'failed'::character varying, NULL::character
varying]::text[]))
Foreign-key constraints:
"sim_phases_sj_id_fkey" FOREIGN KEY (sj_id) REFERENCES sim_jobs(sj_id)
ON DELETE CASCADE

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);
sj_id | sim_phase_name | status | start_datetime |
end_datetime | duration_hrs
-------+----------------+-------------+------------------------+------------------------+--------------
6269 | runsim | in_progress | 2020-04-09 03:39:13-04 |
|
6269 | presim | completed | 2020-04-08 23:11:21-04 | 2020-04-09
03:39:13-04 | 4.46
6267 | runsim | in_progress | 2020-04-09 02:21:38-04 |
|
6267 | presim | completed | 2020-04-08 23:11:21-04 | 2020-04-09
02:21:38-04 | 3.17

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2020-04-09 14:41:22 Re: full text
Previous Message Martin Gainty 2020-04-09 10:08:29 Re: full text