>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
