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

On 24-5-2020 14:00, Richard Yates wrote:
> On Sun, 24 May 2020 11:34:45 +0200, Luuk <[email protected]> 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