query a sorted view by rows

how can I query a sorted view by rows - show rows 50 to 75
or show last 25 rows ?
thanksSam wrote:
> how can I query a sorted view by rows - show rows 50 to 75
> or show last 25 rows ?
> thanks
Views are NOT sorted. If you are using unsupported and unreliable
tricks like TOP 100 PERCENT ... ORDER BY then you'll find that they
break in SQL Server 2005 or maybe even in 2000 under some future fix or
SP.
The solution is to sort when you query the view:
SELECT A1.au_id, A1.au_lname, A1.au_fname
FROM pubs.dbo.authors AS A1
WHERE
(SELECT COUNT(*)
FROM pubs.dbo.authors AS A2
WHERE A1.au_id >= A2.au_id)
BETWEEN 10 AND 15
ORDER BY A1.au_id ;
If you want some more sophisticated paging examples then take a look
at:
http://www.aspfaq.com/show.asp?id=2120
Hope this helps.
David Portas
SQL Server MVP
--|||First of all, there's no need to create an ordered view. Sort your rows when
querying the view, or on the client.
Look up the TOP keyword in Books Online.
http://msdn.microsoft.com/library/d...r />
_13ec.asp
Also lookup RANK(), ROWNUMBER() and DENSE_RANK() in Books Online 2005, if
you're using SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms176102.aspx
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
http://msdn2.microsoft.com/en-us/library/ms173825.aspx
In SQL 2000 these functions do not exist, so you could find a paging method
here:
http://www.aspfaq.com/show.asp?id=2427 (and in the rest of the really nice
neighbourhood of aspfaq.com).
ML