How to specify count() as limit to recursive iterations from a CTE?-CodePudding

I'm trying to limit the amount of recursive iterations from a common table expression using join, but it loops infinitely regardless. What am I doing wrong?

I don't want a fixed iteration limit, but limit according to count(*) of data table dynamically :

create table data(
    name varchar,
    year integer,
    salary double
);

insert into data values 
('a',23, 4100),
('b',22, 1000),
('c',17, 2000);

with recursive myvalues as (
    select name, year, salary from data d
    union all
    select d.name, d.year, d.salary from myvalues v
    join data d on d.name = v.name
    --where v.count <= d.count --error
) select * from myvalues order by name;

CodePudding user response：

Neither V.count nor D.count have been defined anywhere. How do you expect it to know what you're counting?

You're going to have to do two things: 1. get the number of records in the data table into every record in your query, and 2. Create an incrementing variable in your query that increases by one each time through.

The first is accomplished by running another CTE before the one you defined and joining its results to the second CTE.

Like this:

With Recursive tablecount AS (
   Select count(*) as maxn from data
), myvalues AS (
   Select name, year, salary, 1 as N, maxn
   From data d Inner Join tablecount c
  Union All
   Select name, year, salary, v.N 1, v.maxn
   From data d inner join myvalues v
     On d.name = v.name
   Where v.N<v.maxn
)
Select name, year, salary
From myvalues

The first CTE gets the row count into the query. The second CTE is your original query with the addition of field N, tracking the number of times through, and maxn which incorporates the record count. The WHERE clause compares the two; CTEs terminate when no rows are retrieved.