Thanks Set for starting me on the right track. Please see my finalized query
below which gets me to the expected result.
Good to see that you got the result you wanted, Bhavbhuti! I do have two
comments regarding your final result:
1) Why do you use ORDER BY within the CTEs? In the outer select, yes, there
ORDER BY is useful, but within a CTE, I think ORDER BY is mainly useful in
combination with window functions, which aren't available until Firebird 3.
With CTEs like yours, I'd say avoid ORDER BY within the CTE.
2) Using cteFinalOrder is of course one possible option. However, I think it
would be simpler to read the code if you just had joined cteTestGroups and
cteTitleOrder directly in your main select. Though I do admit that is a matter
of preference, and there's nothing wrong in using cteFinalOrder like you do.
Set
