asked on

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#'

Guy Hengel [angelIII / a3]

SELECT TOP 10 * FROM (SELECT top #url.page*10# * FROM products WHERE catID = '#FORM.catId#' ORDER BY somefield asc) ORDER BY somefield DESC

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pigmentarts

ASKER

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>

pigmentarts

ASKER

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.

SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial