Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to just get the last in a recursive query

How to just get the last in a recursive query

From
Shaozhong SHI
Date:


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



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

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

From
Rob Sargent
Date:
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>



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

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

From
Shaozhong SHI
Date:
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>



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

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

From
Rob Sargent
Date:
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>



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

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

From
"David G. Johnston"
Date:
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.

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

From
Steve Midgley
Date:
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 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

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

From
"David G. Johnston"
Date:
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 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)


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.



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

From
"Tchouante, Merlin"
Date:

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 
410-706-4489 * 410-706-1500 fax

 

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu

Please send Mediasite questions to the CITS support email address:  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> 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>

 

 

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

Attachment