Horton

Thread

  1. How to just get the last in a recursive query

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-04T22:14:22Z

    ---------- Forwarded message ---------
From: Shaozhong SHI <shishaozhong@gmail.com>
Date: Mon, 4 Apr 2022 at 23:13
Subject: How to just get the last in a recursive query
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>


In this example, Network Walking in PostGIS · Paul Ramsey
(cleverelephant.ca)
<http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>

3 rows got returns as follows:

 id
---
  6
  3
  1


How to just get the last (namely, 1) in the most efficient way?

Regards, David

  2. Re: How to just get the last in a recursive query

    Rob Sargent <robjsargent@gmail.com> — 2022-04-04T22:16:02Z

    On 4/4/22 16:14, Shaozhong SHI wrote:
>
>
> ---------- Forwarded message ---------
> From: *Shaozhong SHI* <shishaozhong@gmail.com>
> Date: Mon, 4 Apr 2022 at 23:13
> Subject: How to just get the last in a recursive query
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
>
>
> In this example, Network Walking in PostGIS · Paul Ramsey 
> (cleverelephant.ca) 
> <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>
> 3 rows got returns as follows:
>
> |id --- 6 3 1|
> ||
> |How to just get the last (namely, 1) in the most efficient way?|
> Regards, David
reverse the order of the last query and set limit 1

  3. Re: How to just get the last in a recursive query

    Shaozhong SHI <shishaozhong@gmail.com> — 2022-04-04T23:21:52Z

    That is not the most efficient in this case.
How to tell query to deliberately miss out all except the last one is of
interest.
Regards, David

On Monday, 4 April 2022, Rob Sargent <robjsargent@gmail.com> wrote:

> On 4/4/22 16:14, Shaozhong SHI wrote:
>
>
>
> ---------- Forwarded message ---------
> From: Shaozhong SHI <shishaozhong@gmail.com>
> Date: Mon, 4 Apr 2022 at 23:13
> Subject: How to just get the last in a recursive query
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
>
>
> In this example, Network Walking in PostGIS · Paul Ramsey
> (cleverelephant.ca)
> <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>
> 3 rows got returns as follows:
>
>  id
> ---
>   6
>   3
>   1
>
>  How to just get the last (namely, 1) in the most efficient way?
>
> Regards, David
>
> reverse the order of the last query and set limit 1
>

  4. Re: How to just get the last in a recursive query

    Rob Sargent <robjsargent@gmail.com> — 2022-04-04T23:24:42Z

    On 4/4/22 17:21, Shaozhong SHI wrote:
> That is not the most efficient in this case.
> How to tell query to deliberately miss out all except the last one is 
> of interest.
> Regards, David
>
> On Monday, 4 April 2022, Rob Sargent <robjsargent@gmail.com> wrote:
>
>     On 4/4/22 16:14, Shaozhong SHI wrote:
>>
>>
>>     ---------- Forwarded message ---------
>>     From: *Shaozhong SHI* <shishaozhong@gmail.com>
>>     Date: Mon, 4 Apr 2022 at 23:13
>>     Subject: How to just get the last in a recursive query
>>     To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
>>
>>
>>     In this example, Network Walking in PostGIS · Paul Ramsey
>>     (cleverelephant.ca)
>>     <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>>
>>     3 rows got returns as follows:
>>
>>     |id --- 6 3 1|
>>     ||
>>     |How to just get the last (namely, 1) in the most efficient way?|
>>     Regards, David
>     reverse the order of the last query and set limit 1
>
Don't top post

And your definition of "last" is what, exactly

  5. Re: How to just get the last in a recursive query

    David G. Johnston <david.g.johnston@gmail.com> — 2022-04-04T23:32:12Z

    On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <shishaozhong@gmail.com> wrote:

> That is not the most efficient in this case.


Can you prove that statement?  Provide a query that is more efficient.

David J.

  6. Re: How to just get the last in a recursive query

    Steve Midgley <science@misuse.org> — 2022-04-05T00:00:00Z

    On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <shishaozhong@gmail.com> wrote:
>
>> That is not the most efficient in this case.
>
>
> Can you prove that statement?  Provide a query that is more efficient.
>

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 idFROM 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

  7. Re: How to just get the last in a recursive query

    David G. Johnston <david.g.johnston@gmail.com> — 2022-04-05T00:50:09Z

    On Mon, Apr 4, 2022 at 5:00 PM Steve Midgley <science@misuse.org> wrote:

> On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <shishaozhong@gmail.com> wrote:
>>
>>> That is not the most efficient in this case.
>>
>>
>> Can you prove that statement?  Provide a query that is more efficient.
>>
>
> 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 idFROM 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)
>
>
Reading the query now...

I get the desire of the OP - walk the graph and just output the final node
that you fall upon.  The nature of a recursive CTE is that it can/does have
a natural order if the graph produced is linear - thus it has a well
defined last node.  The CTE, however, captures the entire path.  The main
query, which only cares about the final node, then has to reverse the path
and then select the first node.  That is the solution that was provided.

There is no way to get the final node in a path, giving a starting node,
without walking the path.  Or rather, if there is for a given set of data,
a recursive CTE is not the best way to get at it.  I'll admin the presence
of PostGIS makes this a bit harder for me to personally reason about, but
the concept is valid and turning the CTE into a subquery, reversing the
output (which should have an index indicating node order, in addition to
the node id), and doing limit 1 gives the desired answer.

Is there a better way to get that answer for this particular query - I have
no clue - but absent a better answer the OP needs to just take the one
suggestion that does provide a valid answer and assume it is the best
possible.  Since no other suggestion exists the one answer is by definition
also the best answer.

David J.

  8. RE: How to just get the last in a recursive query

    Tchouante, Merlin <mtchouan@umaryland.edu> — 2022-04-05T13:03:44Z

    These worked for me:

These lists the results in reverse order based on the rownum and returns first row, which is actually the last row, just in reversed order.

Oracle:
select *
from (select umab.umab_directory_info.*, rownum
from  umab.umab_directory_info
where pidm = 0
ORDER BY ROWNUM DESC)
WHERE ROWNUM=1;


Pgsql-sql:

select course_id from (select course_main.course_id, row_number() OVER (ORDER BY course_id)
from  course_main
where course_name like '%DO NOT USE%'
ORDER BY row_number DESC limit 1) cm

You need an alias (cm), otherwise you will get an error.

Thanks,
  -- Merlin


Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>

[New UMB Logo]

From: Shaozhong SHI <shishaozhong@gmail.com>
Sent: Monday, April 4, 2022 7:22 PM
To: Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: How to just get the last in a recursive query

CAUTION: This message originated from a non-UMB email system. Hover over any links before clicking and use caution opening attachments.
That is not the most efficient in this case.
How to tell query to deliberately miss out all except the last one is of interest.
Regards, David

On Monday, 4 April 2022, Rob Sargent <robjsargent@gmail.com<mailto:robjsargent@gmail.com>> wrote:
On 4/4/22 16:14, Shaozhong SHI wrote:

---------- Forwarded message ---------
From: Shaozhong SHI <shishaozhong@gmail.com<mailto:shishaozhong@gmail.com>>
Date: Mon, 4 Apr 2022 at 23:13
Subject: How to just get the last in a recursive query
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>>

In this example, Network Walking in PostGIS * Paul Ramsey (cleverelephant.ca)<https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fblog.cleverelephant.ca%2F2010%2F07%2Fnetwork-walking-in-postgis.html&data=04%7C01%7Cmtchouan%40umaryland.edu%7Cccca8e4f951e4780dde008da1691ecb0%7C3dcdbc4a7e4c407b80f77fb6757182f2%7C0%7C0%7C637847113257889566%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=xVii1WKKxTDHx5kZUH49X5tyWwbPkxanjSBoMgpDnCk%3D&reserved=0>

3 rows got returns as follows:


 id

---

  6

  3

  1



How to just get the last (namely, 1) in the most efficient way?

Regards, David
reverse the order of the last query and set limit 1