We help IT Professionals succeed at work.

t-sql LIMIT

pigmentarts
pigmentarts asked
on
1,045 Views
Last Modified: 2012-06-21
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#'
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:


SELECT TOP 10 * FROM (SELECT top #url.page*10# * FROM products WHERE catID = '#FORM.catId#' ORDER BY somefield asc) ORDER BY somefield DESC
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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>

Author

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 Scientist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.