SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jan 2001
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Limit records displayed in Sybase SQL

    Hello,
    I know that in MySQL there's the LIMIT statement to limit the records displayed in a query. Like this
    select * from TableName LIMIT 5

    I need to do that, but for SybaseSQL. Anyone know how that is done?

    Thanks
    Guillermo

  2. #2
    SitePoint Member
    Join Date
    Apr 2002
    Location
    Georgia
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The last time I used Sybase (System 11) there was no such command and you must write your query in such a way as to get back only the amount you want. Example:
    Select * from table where [your condition] and primary key > 1000 and primary key < 5000
    to get back 4000 records at a clip.
    Not what you wanted to hear, I'm afraid.
    BlackandTan

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the SET ROWCOUNT command. It will stop returning rows after you hit the rowcount.

    e.g.
    -- Display 40 rows
    SET ROWCOUNT 40

    SELECT ..

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt, even though i don't use Sybase, i was just wondering how (if) you can specify a row offset to start at? `SET ROWCOUNT ...' just controls the amount of rows returned, correct? can you do the equivalent of `LIMIT 80, 40' in MySQL? just curious.
    Last edited by DR_LaRRY_PEpPeR; May 3, 2002 at 22:01.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately you'd have to do it explicitly in a 'cursor'. Basically you iterate over the rows in the result set and 'ignore' a certain number. Remember that is basically what MySQL does internally -- instead of returning 100 rows to the user it grabs 100 rows and returns the last 20.

    If I wanted to grab the 60th through 80th posts in a forum without cursors (I don't like cursors ) I do is something like this:
    Code:
    -- If we want 60 through 80
    SET ROWCOUNT 60
    
    DECLARE @postid INT
    
    -- This will loop through the 60 postids, and tell us the ID of the 60th post
    SELECT @postid = postid
      FROM post
     WHERE threadid = $threaded
     ORDER BY postid ASC
    
    SET ROWCOUNT 20
    
    -- Now grab 60 though 80
    SELECT *
      FROM post
     WHERE postid >= @postid
    It is probably only a little less efficient than LIMIT since MySQL must take a result set and then iterate through it - but MySQL is, as we know, really, really fast for SELECT queries so perhaps the loss of iterating isnít that bad.

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahh, i see; neat trick. did you have to make changes like that to vB on your forums to handle multiple pages of threads/posts?

    off topic: i notice you explicitly specify "ASC" in your ORDER BY. is that needed in Sybase, or is ASC the default like in MySQL? even if it's the default, do you recommend specifying "ASC" anyway as good practice? i'm just wanting to write my queries as best as possible.

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR
    ahh, i see; neat trick. did you have to make changes like that to vB on your forums to handle multiple pages of threads/posts?
    That is correct; any limits had to be re-written as such, or using a temp table with an auto_increment (identity in Sybase) appended to the front:

    Code:
    SELECT INTO #temp ( ... ) 
    SELECT * 
      FROM post 
     WHERE threadid = $threadid
    
    SELECT *
      FROM #temp
     WHERE rowid BETWEEN 60 AND 80
    Originally posted by DR_LaRRY_PEpPeR

    off topic: i notice you explicitly specify "ASC" in your ORDER BY. is that needed in Sybase, or is ASC the default like in MySQL? even if it's the default, do you recommend specifying "ASC" anyway as good practice? i'm just wanting to write my queries as best as possible.
    Nope, ASC is the ANSI SQL standard default if you leave it off... However it just is force of habit on my end to postfix ASC onto queries... It doesn't use any more processing power that I know of -- but enables people who don't necessarily know the default is ASC to know how I'm sorting. I think it lends to readability a little. It could be like single-statement if blocks in C/PHP without braces:
    PHP Code:
    if( $var )
      
    stuff
    else
      
    stuff

    vs
    .

    if( 
    $var ) {
      
    stuff
    } else {
      
    stuff

    Plus, if they ever decide to change the standard I won't have to re-write my queries!

    Lordy, is it almost 4AM where you are? !! Glad it's a Friday night so I don't have to go to work tomorrow!!

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    Plus, if they ever decide to change the standard I won't have to re-write my queries!
    that's what i was actually thinking. doubtful that'll happen, though!

    thanks for all the info. great as always! i trust your answers.


    Lordy, is it almost 4AM where you are? !!
    no, Missouri's central time. however, it's almost 4AM now (ya know, hour after your post).


Bookmarks

Posting Permissions

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