• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

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#'
2 Solutions
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
Aneesh RetnakaranDatabase AdministratorCommented:
Ypu need to implement Paging

read this

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>
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.
Racim BOUDJAKDJIDatabase 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...

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now