Create auto-Increment column in resultset PostgreSQL

Question

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;

Take a look at this: http://stackoverflow.com/questions/3959692/rownum-in-postgresql — Andres Olarte, Mar 05 '13 at 21:01

MatheusOl · Accepted Answer · 2016-02-14T01:21:57.533

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;

Yes this was what I was aiming for originally. Also as Wrikken stated earlier - this was exactly what I was looking for. Thanks! — Dacto, Mar 05 '13 at 22:06

Create auto-Increment column in resultset PostgreSQL

1 Answers1