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)