>Hi Naomi,
>
>Just tried with View, it's like almost the same. For the approach that you proposed, doing a sub Row_Number() before UNION seems workable.
>
>However, if the records are not in the range of Sub Row_Number(), but fall in the Final Row_Number(), those records will be missing from the selection.
>
>Example
>User select page 2, which will list Row 11th to 20th of the UNION records.
>Page Index = 1, MaxRow = 10
>
>Table 1 contains ID from 1 to 100, return ID 11 to 20 (Row 11th to 20th)
>Table 2 contains ID from 11 to 200, return ID 20 to 30 (Row 11th to 20th)
>Table 3 contains ID from 5 to 150, return ID 50 to 100 (Row 11th to 20th)
>
>Thus, records with ID 11 to 20 from Table 2 and Table 3 will not be selected as the Sub Row_Number will filter them out.
>
>I have set the SET STATISTICS PROFILE ON, but the log is too big to post all, perhaps I can post the specified portion that you needed.
>
>
>Thanks.
You're right, I haven't realized that limitation yesterday. Need to think if there is something else we can do.
If it's not broken, fix it until it is.
My Blog