At 05:54 PM 2/16/2002 +0800, Jason Wong wrote: >On Saturday 16 February 2002 17:48, Dean Householder wrote: > > Is anyone familiar with how exactly to use the TOP command in MSSQL? Is it > > in it's own SQL query or built into the select query? I'm looking at the > > help and it doesn't seem very helpful... > >It's been a while since I last used MS dbs. But I think it goes something >like: > > >SELECT * FROM table TOP 5;
Actually it's SELECT TOP 5 * FROM table; I've had some recent experience attempting to find an equivalent to the LIMIT clause in MSSQL. No one method is really ideal, but here are a couple of ways: 1. Use subselects One method is to use a mixture of the TOP clause along with subselects to get a certain record range out. Let's say that you have a result set and you want to get rows 30-39 (assuming the first row is zero). This is one way: select * from (select top 10 * from (select top 40 * from ... where ... order by ... desc) as ax order by ...) as aa order by ... desc This is pretty odd, but basically what it does is select the top 40 records in reverse order, then from that result set it selects the top 10 (getting records 39-30). The outermost select statement reverses the order again so you get records 30-39. The problem with this method is that it gets slower the higher up in the result set you go. It always has to select as least as many rows as the highest row number you want to get. If your full result set contains 1000 rows, and you need rows 550-560, your statement still has to select at least 560 rows to work. 2. Used a temp table inside a stored procedure This is probably the better solution. Basically what you do is create a stored procedure that selects all of your results into a temp table with an row counter column (field type IDENTITY), and then select the results out of the temp table where (Row >= @Start) AND (Row <= @End). There is a nice article that explains this method in detail here: http://www.15seconds.com/Issue/010308.htm This isn't really related to paging, just more of a general tip: I have found Sybase's Transact-SQL User's Guide on their website to be very helpful in working with MSSQL. For the most part the syntax is exactly the same, and the site is easy to navigate IMHO. Call me crazy, but I've found it generally more helpful than the Books On-Line. YMMV: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
