PSoni, there are a couple of issues with your solution. Granted, it is very easy to do. The fundamental problem, however, is that it is unable to handle an ORDER BY, which is the general use of a 'scrolling window' query such as he has requested. Of course, he did not actually mention that he needed the ORDER BY - it was assumed.
However, let's assume that a sort is not required. The next issue is performance, which was discussed at length in the link I provided.
The differences of note are:
- Your solution does not make use of the STOPKEY option. That means that your entire result set if processed, whereas mine can stop when it hits the 10th record.
- The reason your entire result must be processed is the overhead of the GROUP BY. This accounts for the extra SORT step.
As in the discussion in the original post on this topic, this doesn't make much of a difference in the 12 record example I use. When you have thousands or millions of records in a table with possibly thousands of records being returned in the actual result set that you are trying to 'break up' or 'scroll through', this makes all the difference in the world.