8 Incorporated comment
source | link

Table definitions: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:

Table definitions:

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:

7 added 1334 characters in body
source | link

Sample table data is:

As you can seeThe result I am tryingwould like to achieve an order by the tldid and then by the tlorder column but cannot achieve this.get is as following:

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 tldidlocdescription 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 serial primary keytldorder columnvalue.

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

Table definitions:

Sample data is:

As you can see I am trying to achieve an order by the tldid and then by the tlorder column but cannot achieve this.

tldid is a serial primary key column:

Sample table data is:

The result I would like to get is as following:

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

Table definitions:

6 deleted 1327 characters in body
source | link
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=12tlid = 12  
order by tldid, tldorder

As you can see I am trying to achieve an order by the tldidtldid and then by the tlordertlorder column but cannot achieve this.

tldidtldid is a serial primary key column

                                   Table "public.trip_log_det"
    Column     |       Type        |                          Modifiers                           
---------------+-------------------+--------------------------------------------------------------
 tldid         | integer           | not null default nextval('trip_lod_det_tldid_seq'::regclass)
 tldtlid       | integer           | 
 tldlocation   | integer           | 
 tldaction     | character varying | 
 tldactiondate | date              | 
 tldorder      | integer           | 
Indexes:
    "trip_lod_det_pkey" PRIMARY KEY, btree (tldid)
Foreign-key constraints:
    "trip_lod_det_tldlocation_fkey" FOREIGN KEY (tldlocation) REFERENCES locations(locid)
    "trip_lod_det_tldtlid_fkey" FOREIGN KEY (tldtlid) REFERENCES trip_log(tlid) ON DELETE CASCADE

Table creation statement:

-- Table: public.trip_log_det

-- DROP TABLE public.trip_log_det;

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
)
WITH (
  OIDS=FALSE
);

-- Table: public.locations

-- DROP TABLE public.locations;

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
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.locations
  OWNER TO mtl;

-- Table: public.location_types

-- DROP TABLE public.location_types;

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)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.location_types
  OWNER TO mtl;
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

As you can see I am trying to achieve an order by the tldid and then by the tlorder column but cannot achieve this.

tldid is a serial primary key column

                                   Table "public.trip_log_det"
    Column     |       Type        |                          Modifiers                           
---------------+-------------------+--------------------------------------------------------------
 tldid         | integer           | not null default nextval('trip_lod_det_tldid_seq'::regclass)
 tldtlid       | integer           | 
 tldlocation   | integer           | 
 tldaction     | character varying | 
 tldactiondate | date              | 
 tldorder      | integer           | 
Indexes:
    "trip_lod_det_pkey" PRIMARY KEY, btree (tldid)
Foreign-key constraints:
    "trip_lod_det_tldlocation_fkey" FOREIGN KEY (tldlocation) REFERENCES locations(locid)
    "trip_lod_det_tldtlid_fkey" FOREIGN KEY (tldtlid) REFERENCES trip_log(tlid) ON DELETE CASCADE

Table creation statement:

-- Table: public.trip_log_det

-- DROP TABLE public.trip_log_det;

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
)
WITH (
  OIDS=FALSE
);

-- Table: public.locations

-- DROP TABLE public.locations;

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
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.locations
  OWNER TO mtl;

-- Table: public.location_types

-- DROP TABLE public.location_types;

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)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.location_types
  OWNER TO mtl;
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

As you can see I am trying to achieve an order by the tldid and then by the tlorder column but cannot achieve this.

tldid is a serial primary key column:

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)
);
5 added 894 characters in body
source | link
4 added 639 characters in body
source | link
3 added 1366 characters in body
source | link
2 edited body
source | link
1
source | link