Ordering in postgresql

Question

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query:

select tldid, 
       tldaction, 
       to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
       locdescription,
       lttype, 
       tldorder
from trip_log 
    left join trip_log_det on tlid=tldtlid 
    left join locations on tldlocation=locid 
    left join location_types on loctype=ltid
where tlid = 12 
order by tldid, tldorder

Sample table data is:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

The result I would like to get is as following:

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1    
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

I cannot sort by the last column (tldorder) as that would shuffle around the logic of the trip. I Need to resort the result based on the last column (tldorder) but still keeping the original sequence of locdescription values. Analysing the first two lines of data:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2

I need to swap them around so that it would become:

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4

Same thing for the next two lines

95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1

has to become:

96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4

The problem is that I cannot order by locdescription as these are preset and have to remain in that specific order throughout the query as this is defining spots on a road trip but they can be shuffled around within "themselves".

This is the order of the data in the table.

DAR ES SALAAM
DAR ES SALAAM
TUNDUMA GOING
TUNDUMA GOING
SOLWEZI
SOLWEZI
TUNDUMA RETURN 
TUNDUMA RETURN
DAR ES SALAAM
DAR ES SALAAM

They have to remain like this but the keys of values can be swapped in order to sort them based on the tldorder value.

If I execute the query shown above, I get the same result as in the table:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

I think I have understood the problem. I am ordering by tldid which is a serial and not multiple times a same value. Once I sort also for tldorder, the value is linked to on an only one tldid value. That is why the sort is not working. Is there any way to solve this problem?

Table definitions:

CREATE TABLE public.trip_log_det
(
  tldid integer NOT NULL DEFAULT nextval('trip_lod_det_tldid_seq'::regclass),
  tldtlid integer,
  tldlocation integer,
  tldaction character varying,
  tldactiondate date,
  tldorder integer, -- Ordering for arrival loading offloading and departure actions so user does not get confused!
  CONSTRAINT trip_lod_det_pkey PRIMARY KEY (tldid),
  CONSTRAINT trip_lod_det_tldlocation_fkey FOREIGN KEY (tldlocation)
      REFERENCES public.locations (locid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.locations
(
  locid integer NOT NULL DEFAULT nextval('locations_locid_seq'::regclass),
  locdescription character varying,
  loctype integer,
  CONSTRAINT locations_pkey PRIMARY KEY (locid),
  CONSTRAINT "loctype->lttype" FOREIGN KEY (loctype)
      REFERENCES public.location_types (ltid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.location_types
(
  ltid integer NOT NULL DEFAULT nextval('location_types_ltid_seq'::regclass),
  lttype character varying,
  ltdeparturedate boolean NOT NULL DEFAULT false,
  ltarrivaldate boolean NOT NULL DEFAULT false,
  ltloadingdate boolean NOT NULL DEFAULT false,
  ltoffloadingdate boolean NOT NULL DEFAULT false,
  CONSTRAINT location_types_pkey PRIMARY KEY (ltid)
);

Answer 1

After the edit, the question is (at last!) clear. The problem is that the ordering you want is complicated. It's a "gaps-and-islands" problem. You want first to isolate "islands" with same location, i.e. consecutive (when ordered by tldid) rows that have same location and then reorder the rows in those islands based on the tldorder.

One way to solve this:

with trip_log_det_ordered as
  ( select 
        tldid, 
        tldaction, 
        tldactiondate, 
        locdescription,
        lttype, 
        tldorder, 
        case when locid <> lag(locid) over (order by tldid) then 1 else null end
            as location_change
    from trip_log 
        left join trip_log_det on tlid=tldtlid 
        left join locations on tldlocation=locid 
        left join location_types on loctype=ltid
    where tlid = 12 
  )
select 
    tldid, 
    tldaction, 
    to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
    locdescription,
    lttype, 
    tldorder
from 
    trip_log_det_ordered
order by 
    count(location_change) over (order by tldid
                                 rows between unbounded preceding
                                          and current row),
    tldorder ;

The else null and the rows between unbounded preceding and current row can be removed, as they are the default.

If you add the count(...) in the select list, you can see the islands numbering. First island will have 0 (location changes), second island will have 1, third will have 2, etc...

Further notes:

• The query would be much, much simpler if the trip_log_det (tldactiondate) was populated and could be used for the ordering.