• WITH RECURSIVE... [Re: Column headings in csv]

    From Luuk@21:1/5 to Richard Yates on Sun May 24 15:21:57 2020
    On 24-5-2020 14:00, Richard Yates wrote:
    On Sun, 24 May 2020 11:34:45 +0200, Luuk <luuk@invalid.lan> wrote:


    Ok, example, selecting the two lowest and the two highest values:

    WITH RECURSIVE cte (n) AS
    (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
    )
    (SELECT *
    FROM cte
    order by n
    limit 2 )
    union all
    (SELECT *
    FROM cte
    order by n desc
    limit 2 )
    order by n
    ;

    1) The 'with recursive' creates a temporary view containing 5 records
    from 1 until (including) 5.

    2) In the 'SELECT * FROM cte order by n limit 2', the ORDER BY is needed
    to make sure where get the first two records. Because of the LIMIT there
    is also a need to know where to start, this can only be done using ORDER BY. >>
    3) after the union all we do the same select, again with 'order by' to
    make sure the highest two values are selected.

    4) the final order by is needed to produce 1,2,4,5 and not 1,2,5,4

    Sorry, that example is beyond me and I do not know what it is intended
    to show. And I've never run across RECURSIVE before.



    Try this:

    WITH RECURSIVE cte (n) AS
    (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
    )
    SELECT * FROM cte;


    The 'WITH [RECURSIVE]' defines a temporry table with the data from the
    select statement that is following it.

    The 'SELECT * from cte' shows the output of the select, in this case
    numbers from 1 to 5;

    In this case it is used to create a temporary table to have some data
    for a simple example....

    --
    Luuk

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)