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
svein.erling.tysvaer@... [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 thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • 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.