Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Question Unanswered: Anyway to emulate LIMIT Start,Count?

    Does anyone know of a way to emulate the LIMIT clause that is available in Oracle and MySQL?

    I could greatly improve my performance on my web application if I could figure out how to do it. I am using TOP right now to just pull back the records needed, but as with any page with many results, once you get 10 pages in you're pulling way too many records accross the wire.

    The LIMIT clause works great in the other DBMS's, but MS SQL 2K does not have it.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    set rowcount 100
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2004
    Posts
    9
    That doesn't deal with the offset right??

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It just limits the number of rows returned to the client to 100 (or whatever number you specify)

    Don't forget to set it to 0 to restore the default functionality - RETURN ALL ROWS.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2004
    Posts
    9
    I already get that with TOP so there is no need for this.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want LIMIT 30,20 (start at offset 30, i.e. 31st row, and return 20 rows) like this:

    Code:
    select *
      from (
           select top 20
                  foo
                , bar
             from ( 
                  select top 50
                         foo
                       , bar
                    from yourtable
                  order
                      by bar desc
                  )
           order
               by bar asc       
           )
    order
        by bar desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96

  8. #8
    Join Date
    Jun 2004
    Posts
    9
    I like the multiple subselect idea but don't seem to be able to get it to work. The sql errors out when trying to do an order by on a subselect.

    For example - this errors out:

    select top 20 AccountID,[Name] from (
    select top 50 AccountID,[Name]
    from CRDACCOUNT
    order by AccountID desc
    ) order by AccountID
    Give an "Incorrect syntax near the keyword 'order'." on the last line.

    Ideas?

    PS: Thanks for the great replies so far everyone!!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm 99 5/8% certain that you are just missing an alias, something like:
    Code:
    SELECT TOP 20 AccountID, [Name]
       FROM (SELECT TOP 50 AccountID, [Name]
          FROM CRDACCOUNT
          ORDER BY AccountID DESC) AS zz
       ORDER BY AccountID
    -PatP

  10. #10
    Join Date
    Oct 2003
    Posts
    268
    wow, I just posted a question asking nearly the identical thing. So far it doesn't look good. My options are:

    - Using an ORDER BY on column and maintain a bookmark position. This works but I can't get decent performance out of it.
    - Paging techniques. Having trouble getting these to work right as well.

    Let me know if you find an acceptable solution.

  11. #11
    Join Date
    Jun 2004
    Posts
    9
    Yep - it was the alias. Here is the first query formatted with the aliases:

    select *
    from (
    select top 20
    foo, bar
    from (
    select top 50
    foo, bar
    from yourtable
    order
    by bar desc
    ) as tbl1
    order
    by bar asc
    ) as tbl2
    order
    by bar desc
    I guess the trick now is to figure out how to have multi fields in the ORDER BY clause.

    Thanks for the help that has been given!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •