t-sql LIMIT

ok this one no longer works in t-sql


            SELECT  * FROM products WHERE catID = '#FORM.catId#'
            LIMIT #url.page*10-10#, 20;


this is what it should say in english:

select all from the proudcts table where the catid = my form and limit the results by  #url.page*10-10#, 20;


i have tried this but does not work

SELECT top #url.page*10-10#, 20; * FROM products WHERE catID = '#FORM.catId#'
LVL 12
pigmentartsAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Ypu need to implement Paging

read this

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:


SELECT TOP 10 * FROM (SELECT top #url.page*10# * FROM products WHERE catID = '#FORM.catId#' ORDER BY somefield asc) ORDER BY somefield DESC
0
 
pigmentartsAuthor Commented:
Paging look good, but this one does work, just want to tget it working in t-sql right as it worked fine when i was on mysql as coldfusion does all the work, i just need to get the query to say the same thing in t-sql as it did before>

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'ORDER'.
 
397 : <cffunction name="getProducts" returntype="query" hint="get the products from a certain department">
398 :       <cfquery name="products" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
399 :             SELECT TOP 10 * FROM (SELECT top #url.page*10# * FROM products WHERE catID = '#FORM.catId#' ORDER BY name asc) ORDER BY name DESC
400 :       </cfquery>
401 :       <cfreturn products>
0
 
pigmentartsAuthor Commented:
this worked before like this




SELECT * FROM products WHERE catID = '#FORM.catId#' LIMIT #url.page*10-10#, 20


url.page changes cf side so that each time i get the next set of results 20 at a time. now this worked like until we moved to MS SQL and all i think is it needs the correct syntax for T-SQL.
0
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
I suggest you implement paging at ColdFusion level as it will limit round trips and improve performance caching...There should be an option such as startrow= endrow= if I remember right...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.