Oops, forgot the ordering and of course, it can be optimized like this:
with recursive CTE_NR as
(select
1 as NR,
t.ID,
(select first 1 skip 5 t1.ID from TBL t1 where t1.ID > t.ID order by
t1.ID) as NXT
from (select first 1 t.ID from TBL t order by t.ID) t
union all select
NR + 1,
t.ID,
(select first 1 skip 5 t1.ID from TBL t1 where t1.ID > t.ID order by
t1.ID) as NXT
from CTE_NR t0
join TBL t on t.ID = t0.NXT
where NR < 1024)
select NR, ID from CTE_NR n0
returning 1024 records and still skipping 5...
Thomas
Am 29.07.2014 10:32, schrieb Thomas Beckmann [email protected]
[firebird-support]:
> Hi Svein, hi Sean,
>
> my solution would look similar to what you pointed out, Sean, and I
> agree, execute block usually provides better readability and
> recursion depth is a problem:
>
> with recursive CTE_NR as (select 1 as NR, t.ID, (select first 1 t1.ID
> from TBL t1 where t1.ID > t.ID) as NXT from (select first 1 t.ID from
> TBL t order by t.ID) t union all select NR + 1, t.ID, (select first 1
> t1.ID from TBL t1 where t1.ID > t.ID) as NXT from CTE_NR t0 join TBL
> t on t.ID = t0.NXT where NR < 1024)
>
> select NR, ID from CTE_NR where mod(NR, 5) = 0
>
> Thomas
>
>
> Am 29.07.2014 09:47, schrieb Svein Erling Tysvær
> [email protected] [firebird-support]:
>>
>>
>>> Thomas,
>>>
>>>> I usually solve this kind of problem with recusive CTEs
>>>> providing a counter for the records returned, thus a "simple"
>>>> select statement can nicely filter on this kind of property
>>>> without row numbers functionality.
>>>
>>> Can you provide an example, please.
>>>
>>> I, for one, can't imagine how a CTE could be used for such a
>>> purpose.
>>
>> My imagination may be slightly more vivid than yours, Sean, I even
>> think I answered something on this list with a similar recursive
>> CTE a few years ago.
>>
>> WITH RECURSIVE TMP(Level, PK, <fields>) as (SELECT 1, min(PK),
>> <whatever> FROM <table> WHERE <whatever> UNION ALL SELECT t.Level +
>> 1, t1.PK, <fields> FROM TMP t JOIN <table> t1 on t.PK < t1.PK LEFT
>> JOIN <table> t2 on t2.PK > TMP.PK and t2.PK < t1.PK WHERE
>> <whatever> and t2.PK is null) SELECT * FROM TMP WHERE MOD(Level, 5)
>> = 0
>>
>> This has the severe drawback that it doesn't work if there's more
>> than 1024 records (I think, at least there's a very limited max
>> depth of recursion), I consider it less intuitive than EXECUTE
>> BLOCK, and expect it to be slower. But I agree that it would be
>> interesting to see Thomas' solution.
>>
>> Don't know whether windowing functions can be used in the WHERE
>> clause, but when Firebird 3 is released, it would be tempting to
>> try things like
>>
>> WHERE MOD(ROW_NUMBER() OVER (ORDER BY <something>), 5) = 2
>>
>> Set
>>
>>
>
--
Mit freundlichen Grüßen,
Thomas Beckmann
Diplom-Informatiker
Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail [email protected] <mailto:[email protected]>
ASSFINET-Logo
*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
[email protected] <mailto:[email protected]> • www.assfinet.de
<http://www.assfinet.de/>
Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
