public inbox for [email protected]
help / color / mirror / Atom feedHow to just get the last in a recursive query
8+ messages / 5 participants
[nested] [flat]
* How to just get the last in a recursive query
@ 2022-04-04 22:14 Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Shaozhong SHI @ 2022-04-04 22:14 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
---------- Forwarded message ---------
From: Shaozhong SHI <[email protected]>
Date: Mon, 4 Apr 2022 at 23:13
Subject: How to just get the last in a recursive query
To: PostGIS Users Discussion <[email protected]>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
@ 2022-04-04 22:16 ` Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Rob Sargent @ 2022-04-04 22:16 UTC (permalink / raw)
To: [email protected]
On 4/4/22 16:14, Shaozhong SHI wrote:
>
>
> ---------- Forwarded message ---------
> From: *Shaozhong SHI* <[email protected]>
> Date: Mon, 4 Apr 2022 at 23:13
> Subject: How to just get the last in a recursive query
> To: PostGIS Users Discussion <[email protected]>
>
>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
@ 2022-04-04 23:21 ` Shaozhong SHI <[email protected]>
2022-04-04 23:24 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:32 ` Re: How to just get the last in a recursive query David G. Johnston <[email protected]>
2022-04-05 13:03 ` RE: How to just get the last in a recursive query Tchouante, Merlin <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Shaozhong SHI @ 2022-04-04 23:21 UTC (permalink / raw)
To: Rob Sargent <[email protected]>; +Cc: [email protected] <[email protected]>
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 <[email protected]> wrote:
> On 4/4/22 16:14, Shaozhong SHI wrote:
>
>
>
> ---------- Forwarded message ---------
> From: Shaozhong SHI <[email protected]>
> Date: Mon, 4 Apr 2022 at 23:13
> Subject: How to just get the last in a recursive query
> To: PostGIS Users Discussion <[email protected]>
>
>
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
@ 2022-04-04 23:24 ` Rob Sargent <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Rob Sargent @ 2022-04-04 23:24 UTC (permalink / raw)
To: Shaozhong SHI <[email protected]>; +Cc: [email protected] <[email protected]>
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 <[email protected]> wrote:
>
> On 4/4/22 16:14, Shaozhong SHI wrote:
>>
>>
>> ---------- Forwarded message ---------
>> From: *Shaozhong SHI* <[email protected]>
>> Date: Mon, 4 Apr 2022 at 23:13
>> Subject: How to just get the last in a recursive query
>> To: PostGIS Users Discussion <[email protected]>
>>
>>
>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
@ 2022-04-04 23:32 ` David G. Johnston <[email protected]>
2022-04-05 00:00 ` Re: How to just get the last in a recursive query Steve Midgley <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2022-04-04 23:32 UTC (permalink / raw)
To: Shaozhong SHI <[email protected]>; +Cc: Rob Sargent <[email protected]>; pgsql-sql <[email protected]>
On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <[email protected]> wrote:
> That is not the most efficient in this case.
Can you prove that statement? Provide a query that is more efficient.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 23:32 ` Re: How to just get the last in a recursive query David G. Johnston <[email protected]>
@ 2022-04-05 00:00 ` Steve Midgley <[email protected]>
2022-04-05 00:50 ` Re: How to just get the last in a recursive query David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Steve Midgley @ 2022-04-05 00:00 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Shaozhong SHI <[email protected]>; Rob Sargent <[email protected]>; pgsql-sql <[email protected]>
On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <[email protected]>
wrote:
> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 23:32 ` Re: How to just get the last in a recursive query David G. Johnston <[email protected]>
2022-04-05 00:00 ` Re: How to just get the last in a recursive query Steve Midgley <[email protected]>
@ 2022-04-05 00:50 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2022-04-05 00:50 UTC (permalink / raw)
To: Steve Midgley <[email protected]>; +Cc: Shaozhong SHI <[email protected]>; Rob Sargent <[email protected]>; pgsql-sql <[email protected]>
On Mon, Apr 4, 2022 at 5:00 PM Steve Midgley <[email protected]> wrote:
> On Mon, Apr 4, 2022 at 4:32 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Mon, Apr 4, 2022, 16:21 Shaozhong SHI <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: How to just get the last in a recursive query
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Re: How to just get the last in a recursive query Rob Sargent <[email protected]>
2022-04-04 23:21 ` Re: How to just get the last in a recursive query Shaozhong SHI <[email protected]>
@ 2022-04-05 13:03 ` Tchouante, Merlin <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Tchouante, Merlin @ 2022-04-05 13:03 UTC (permalink / raw)
To: Shaozhong SHI <[email protected]>; Rob Sargent <[email protected]>; +Cc: [email protected] <[email protected]>
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
[email protected]<mailto:[email protected]>
410-706-4489 * 410-706-1500 fax
Please send Blackboard questions to the CITS support email address: [email protected]<mailto:[email protected]>
Please send Mediasite questions to the CITS support email address: [email protected]<mailto:[email protected]>
[New UMB Logo]
From: Shaozhong SHI <[email protected]>
Sent: Monday, April 4, 2022 7:22 PM
To: Rob Sargent <[email protected]>
Cc: [email protected]
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 <[email protected]<mailto:[email protected]>> wrote:
On 4/4/22 16:14, Shaozhong SHI wrote:
---------- Forwarded message ---------
From: Shaozhong SHI <[email protected]<mailto:[email protected]>>
Date: Mon, 4 Apr 2022 at 23:13
Subject: How to just get the last in a recursive query
To: PostGIS Users Discussion <[email protected]<mailto:[email protected]>>
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%...;
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
Attachments:
[image/jpeg] image001.jpg (11.5K, 3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-04-05 13:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-04 22:14 How to just get the last in a recursive query Shaozhong SHI <[email protected]>
2022-04-04 22:16 ` Rob Sargent <[email protected]>
2022-04-04 23:21 ` Shaozhong SHI <[email protected]>
2022-04-04 23:24 ` Rob Sargent <[email protected]>
2022-04-04 23:32 ` David G. Johnston <[email protected]>
2022-04-05 00:00 ` Steve Midgley <[email protected]>
2022-04-05 00:50 ` David G. Johnston <[email protected]>
2022-04-05 13:03 ` Tchouante, Merlin <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox