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)
);