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
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
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 …
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. 
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:
-- 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.
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. 
Originally posted by DR_LaRRY_PEpPeR
ahh, i see; neat trick.
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:
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:
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!!
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! :tup: 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). 