Обсуждение: How to generate the order if you know the next item for each item?
Hi, all:
I have two tables one is a lookup for all the tasks, another table stores the next task for each task, I want to generate a list of tasks with its sequence number, can I do it in one query?
Table task
id name
1 postoffice
2 grocery store
3. walk dog
4. swimming
5 roller skate
7 biking
table next_task
task next_task
3 7
7 1
1 4
4 2
2 5
and I want to get the list in order
task task_order
3 (walk dog) 1
7 (biking) 2
1 (postoffice) 3
4 (swimming) 4
2 (growcery store) 5
5 (roller skate) 6
It might be just very simple and I have tried but somehow I am blocked.
Thanks in advance
-Rockdale
On Tue, Aug 3, 2021 at 8:40 AM Rockdale Green <rockdale.green@gmail.com> wrote:
can I do it in one query?
Yes.
It might be just very simple and I have tried but somehow I am blocked.
The feature you need is a recursive CTE (WITH RECURSIVE clause). It requires an initial row (which in this case is task 3, the only task that doesn't appear as a task_order value) and a query to retrieve the next row(s) using the information available in the previous iteration.
The documentation and/or online resources can walk you through the specifics.
David J.
We have some ordered processing that we need to do that can involve reading through 10s of thousands of records, with a lot of conditional processing. We use functions almost exclusively for this, returning a table of results.
On Tue, 3 Aug 2021 at 18:22, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Aug 3, 2021 at 8:40 AM Rockdale Green <rockdale.green@gmail.com> wrote:can I do it in one query?Yes.It might be just very simple and I have tried but somehow I am blocked.The feature you need is a recursive CTE (WITH RECURSIVE clause). It requires an initial row (which in this case is task 3, the only task that doesn't appear as a task_order value) and a query to retrieve the next row(s) using the information available in the previous iteration.The documentation and/or online resources can walk you through the specifics.David J.
Thanks David. With RECURSIVE I got what I needed.
On Tue, Aug 3, 2021 at 12:22 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Aug 3, 2021 at 8:40 AM Rockdale Green <rockdale.green@gmail.com> wrote:can I do it in one query?Yes.It might be just very simple and I have tried but somehow I am blocked.The feature you need is a recursive CTE (WITH RECURSIVE clause). It requires an initial row (which in this case is task 3, the only task that doesn't appear as a task_order value) and a query to retrieve the next row(s) using the information available in the previous iteration.The documentation and/or online resources can walk you through the specifics.David J.
Thanks, I do not need functions at this time but will keep that in mind in the future.
On Thu, Aug 5, 2021 at 10:33 AM Tony Shelver <tshelver@gmail.com> wrote:
We have some ordered processing that we need to do that can involve reading through 10s of thousands of records, with a lot of conditional processing. We use functions almost exclusively for this, returning a table of results.On Tue, 3 Aug 2021 at 18:22, David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Aug 3, 2021 at 8:40 AM Rockdale Green <rockdale.green@gmail.com> wrote:can I do it in one query?Yes.It might be just very simple and I have tried but somehow I am blocked.The feature you need is a recursive CTE (WITH RECURSIVE clause). It requires an initial row (which in this case is task 3, the only task that doesn't appear as a task_order value) and a query to retrieve the next row(s) using the information available in the previous iteration.The documentation and/or online resources can walk you through the specifics.David J.