Thanks for the reply, Eric; that's definitely worth remembering.
I have been doing some further digging on this and have found lots of interesting stuff.
Some seem to think using the new ROW_NUMBER() function in SQL Server 2005 is not the most efficient way to do paging anway...
There are links off of this article to other ways of doing it which are, according to some, more efficient with large result sets.
There is an interesting comparison of techniques here (unfortunately not including ROW_NUMBER):
This also goes into more detail on the non-unique items in ORDER BY column issue.
Unfortunately though, I need to do sorting too, and also sort on the 'in-line view' query result. For example, take the following code:
SELECT PollID, AddedDate, AddedBy,
QuestionText, IsCurrent, IsArchived,
ArchivedDate, IsApproved, ExpireDate,
(SELECT SUM(Votes) FROM vs_PollOptions WHERE PollID = vs_Polls.PollID) AS Votes
ORDER BY AddedBy DESC
In this case, I am sorting on the 'AddedBy' column. But I also need to be able to sort on the 'Votes' column. However, as this column value is dynamically generated as the result-set is built, I cannot sort on it! It looks as though to acheive this I have no choice but to use a VIEW or INNER JOIN (sigh!).