Re: How to just get the last in a recursive query - Mailing list pgsql-sql

Just to share the SQL from that example

WITH RECURSIVE walk_network(id, segment) AS (  SELECT id, segment     FROM network     WHERE id = 6  UNION ALL  SELECT n.id, n.segment    FROM network n, walk_network w    WHERE ST_DWithin(      ST_EndPoint(w.segment),      ST_StartPoint(n.segment),0.01)
)
SELECT id
FROM walk_network

David J (kind of off-topic): There's no order by in the original query, so I could imagine that adding any order by clause at all would make the query less efficient. But maybe it could become more efficient if the planner picks a better index as a result?

David (OP): My main point is that in this example, since no order by clause is provided, it is meaningless to talk about a "last" or "first" item. SQL, afaik, is not required to produce the results in any order whatsoever, when no order by clause is provided (corrections welcome if that's not accurate). So while you might grab the last item somehow this time, it might not be the last item, the next time you run the query. So I'd say you should add an appropriate order by query, and then you can measure "ASC" vs "DESC" with "LIMIT 1" to see if either one is less efficient. (I'm in David J's camp that it's unlikely to make any difference)

Steve