The result of this query is only meaningful if the order from path
is preserver. However, upon my Left Inner Join the result is scrambled. I was thinking that I could just create a new column in the resultset that is just like an index of the result and then as the final clause of the entire sql add an ORDER BY idx
.
This is my query without trying to preserve the final order:
SELECT path.*, network_link.v0prt
FROM (SELECT * // Need order preserved from this one
FROM shortest_path_shooting_star(
'SELECT gid as id, source::integer,
target::integer, distance::double precision as cost,
x1, y1, x2, y2, rule, to_cost FROM
network_link as net ORDER BY net.gid', 9, 1, false, false)) as path
LEFT OUTER JOIN
(SELECT DISTINCT gid, v0prt FROM network_link) as network_link
ON (network_link.gid=path.edge_id);
Any insight would be great.
Thanks. And my attempt to add an indexing value and ORDER BY (which doesn't work).
SELECT path.*, network_link.v0prt
FROM (SELECT incr(0) as idx, *
FROM shortest_path_shooting_star(
'SELECT gid as id, source::integer,
target::integer, distance::double precision as cost,
x1, y1, x2, y2, rule, to_cost FROM
network_link as net ORDER BY net.gid', 9, 1, false, false)) as path
LEFT OUTER JOIN
(SELECT DISTINCT gid, v0prt FROM network_link) as network_link
ON (network_link.gid=path.edge_id)
ORDER BY idx;
To add a new AUTO_INCREMENT integer column named c : ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .
PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
To preserve the order returned by shortest_path_shooting_star
(if it gives no other way), you can use the window function row_number
to keep track of the original order, and them ORDER BY
its result:
SELECT path.*, network_link.v0prt
FROM (SELECT row_number() OVER() AS row_number, *
FROM shortest_path_shooting_star(
'SELECT gid as id, source::integer,
target::integer, distance::double precision as cost,
x1, y1, x2, y2, rule, to_cost FROM
network_link as net ORDER BY net.gid', 9, 1, false, false)) as path
LEFT OUTER JOIN
(SELECT DISTINCT gid, v0prt FROM network_link) as network_link
ON (network_link.gid=path.edge_id)
ORDER BY path.row_number;
UPDATE:
From PostgreSQL version 9.4 and newer, a better method would be using WITH ORDINALITY
:
SELECT path.*, network_link.v0prt
FROM shortest_path_shooting_star(
'SELECT gid as id, source::integer,
target::integer, distance::double precision as cost,
x1, y1, x2, y2, rule, to_cost FROM
network_link as net ORDER BY net.gid', 9, 1, false, false)
) WITH ORDINALITY AS path
LEFT OUTER JOIN
(SELECT DISTINCT gid, v0prt FROM network_link) as network_link
ON (network_link.gid=path.edge_id)
ORDER BY path.ordinality;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With