PAGING? Is there something similar to MYSQL "LIMIT"
-
Hello,
I have a developer who would like to have a pre-determined number of record results returned similar to a web search engine ie: initial results display records 1-10, then clicking next will return records 11-20, 21-30, etc increments until end of record is found.
He mentioned that MYSQL has a reserved word "LIMIT" that accomplishes this. He is using cold fusion to dervelop the web app and tHe database is MSSQL 2000 w/SP3.
Any help/ suggestions is much apprecitaed.
Many thanks. Jeff
-
LIMIT is a bit more specialised than the nearest SQL equivalent which is the TOP predicate
SELECT TOP 20 C.Firstname, C.Surname
FROM Customers AS C
ORDER BY C.FirstName
There is also SET ROWCOUNT that achieves something similar.
To get records 11 to 20 I would select the top 20 in ascending order and put them in a temp table or table variable, then from that select the top 10 in reverse order.
Not perfect but it works.
What language is your developer using? If it is .NET then you can use a dataset to achieve the same thing in code.
-
One more thought, JavaScript can also be used to hide and unhide panels containing each individual dataset. The big problem, as with the two top statements, is when result sets become very large.
Everett Wilson
ewilson10@yahoo.com -
There are many ways to implement a potential solution, many suffer under the strain of a large dataset. Sql 200 is based on the SQL 92 standards and thus does not contain LIMIT as that was an Oracle addon if I remember correctly.
My suggestion is use ADO with a server side cursor to set up paging. This is roughly the same as the way LIMIT works as SQL can implement proper server side cursors. You can set up range size per page and move to each absolute page.
-
Or you can use the top clause with an order by. You just have to remember the last data shown.
This is and addition to Davif Pool's solution
So you query could look like if if would come from a Stored Proc with @Firstname as "max" parameter:
SELECT TOP 20 C.Firstname, C.Surname
FROM Customers AS C
Where C.Firstname > @Firstname
ORDER BY C.FirstName
Bye
Gabor -
1. If you are using an ADO Connection and Recordset from the web side, you can use the PageSize and AbsolutePage properties. Refer to MSDN Library at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdpropagesize.asp
Here's the description of PageSize from the ADO 2.8 API Reference:
PageSize Property
Indicates how many records constitute one page in the Recordset.
Settings and Return Values
Sets or returns a Long value that indicates how many records are on a page. The default is 10.
Remarks
Use the PageSize property to determine how many records make up a logical page of data. Establishing a page size allows you to use the AbsolutePage property to move to the first record of a particular page. This is useful in Web-server scenarios when you want to allow the user to page through data, viewing a certain number of records at a time.
This property can be set at any time, and its value will be used for calculating the location of the first record of a particular page.
See Also
Visual Basic Example | Visual C++ Example | Visual J++ Example
AbsolutePage Property | PageCount Property
Applies To
Recordset Object
2. If coldfusion doesn't offer PageSize/AbsolutePage, then these queries below should work. Part of this technique assumes that you have a unique key on the underlying table. The unique key is needed as a tie-breaker in case you have a number of same-name records in your database (e.g., "John Smith"). I presume your web-app builds the query string in the app and then sends it down to SQL Server.
2A. For the first page:
SELECT TOP 20 C.Firstname, C.Surname, C.CustID,
'' + C.Surname + C.Firstname + C.CustID AS combined_key
FROM Customers AS C
ORDER BY C.Surname, C.Firstname, C.CustID
2B. To roll forward one page, fill $LASTKEY$ with the last combined_key value from the currently displayed page:
SELECT TOP 20 C.Firstname, C.Surname, C.CustID
'' + C.Surname + C.Firstname + C.CustID AS combined_key
FROM Customers AS C
WHERE ('' + C.Surname + C.Firstname + C.CustID) > $LASTKEY$
ORDER BY C.Surname, C.Firstname, C.CustID
2C. To roll backward one page, fill $FIRSTKEY$ with the first combined_key value from the currently displayed page. Note this is more complicated because the selection of 20 items requires a reverse-ordering from the desired display ordering. However, this should work.
SELECT * FROM (
SELECT TOP 20 C.Firstname, C.Surname, C.CustID
'' + C.Surname + C.Firstname + C.CustID AS combined_key
FROM Customers AS C
WHERE ('' + C.Surname + C.Firstname + C.CustID) < $FIRSTKEY$
ORDER BY C.Surname DESC, C.Firstname DESC, C.CustID DESC
) AS cpage
ORDER BY cpage.Surname, cpage.Firstname, cpage.CustID
Good luck!
Regards,Bob Monahon
-
This may look simple - and it is - but try and use this kind of script :
"SELECT TOP 10 FROM Product WHERE ProductID NOT IN (SELECT TOP 10 FROM Product ORDER BY ProductID) ORDER BY ProductID"
Simple script that can solve your paging problems !
Cheers and be good
-
You may find this article useful.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply