However MySQL seemed more than adequately equiped to handle the challenge because it has a powerfull feature for doing that (LIMIT clause) where as in SQL2005 you handle that with a far more complex construct of a CTE and ROW_NUMBER().

Walter, you seem intent on characterizing every SQL 2005 construct that you feel you wouldn't use as "far more complex"....yet without some measure, these statements are meaningless.

This seems to be a common patttern in your posts....you say things like "my approach is more generic", "your approach is more complicated"...but you don't say why.

Sure, LIMIT is nice - but the advantage of ROW_NUMBER is that I can leverage it beyond the statement itself. I use it, and it gives me a value I can use elsewhere. Other ranking functions (like RANK and DENSE_RANK functions to number ties with the same ranking) are also helpful.

LIMIT, on the other hand, doesn't give you access to the value, outside the LIMIT statement itself. You mentioned that you used a variable incrementing syntax (maybe I misread, you said it might work in mysql, but wouldn't work in SQL 2000, though that surprised me that it wouldn't)

In this case I would say that this particular challenge was won by MySQL.

I'm stunned that you would think MySQL 'won' here. I was never looking for a 'winner'.

Less code
More flexible (You can easily reuse the code for any table)

If more tables need to be paged, the SP can be generated. And in that case, the 'less code' factor is a zero-argument.


More portable as it has less dependencies on the vendor and version of the backend database.

HUH?? How's that? You're already using it for MySQL (and I'm targeting mine to SQL Server 2005). Database Portability was never a goal. However, you targeted your solution to VFP. As I previously stated, placing the solution in the database makes it easier to expose this solution as an API to other development environments.

You are correct, I don't need to use SUBSTRING, that was a good observation. But again, you seem to view every construct that you yourself don't use as "too complex", etc.