More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Selecting rows N to M without Oracles rownum in Sybase?
Sybase does not have a direct equivalent to Oracle's rownum but its functionality can be emulated in a lot of cases.
If you are simply trying to retrieve the first N rows of a table, then simple use:
set rowcount
replacing <N> with your desired number of rows. (set rowcount 0 restores normality.) If it is simply the last N rows, then use a descending order-by clause in the select.
1. set rowcount
2. go
1. select foo
2. from bar
3. order by barID desc
4. go
If you are trying to retrieve rows 100 to 150, say, from a table in a given order. You could use this to retrieve rows for a set of web pages, but there are probably more efficient ways using cursors or well written queries or even Sybperl! The general idea is select the rows into a temporary table adding an identity column at the same time. Only select enough rows to do the job using the rowcount trick. Finally, return the rows from the temporary table where the identity column is between 100 and 150. Something like this:
set rowcount 150
select pseudo_key = identity(3),
col1,
col2
into #tempA
from masterTable
where clause...
order by 2,3
select col1,col2 from #tempA where pseudo_key between 100 and 150
Remember to reset rowcount back to 0 before issuing any more SQL or you will only get back 150 rows!
A small optimisation would be to select only the key columns for the source table together with the identity key. Once you have the set of rows you require in the temporary table, join this back to the source using the key columns to get any data that you require.
An alternative, which might be better if you needed to join back to this table a lot, would be to insert enough rows to cover the range as before, but then delete the set of unwanted rows. This would be a very efficient mechanism if the majority of your queries involved the first few rows of a table. A typical application for this might be a search engine displaying relevant items first. The chances are that the user is going to be bored after the first couple of pages and go back to playing 'Internet Doom'.
set rowcount 150
select col1,
col2
into #tempA
from masterTable
where clause...
set rowcount 100
delete #tempA
Sybase does not guarantee to return rows in any particular order, so the delete may not delete the correct set of rows. In the above example, you should add an order-by to the 'select' and build a clustered index on a suitable key in the temporary table.
The following stored proc was posted to the Sybase-L mailing list and uses yet another mechanism. You should check that it works as expected in your environment since it relies on the fact a variable will be set using the last row that is returned from a result set. This is not published behaviour and is not guaranteed by Sybase.
CREATE PROCEDURE dbo.sp_get_posts
@perpage INT,
@pagenumber INT
WITH RECOMPILE
AS
-- if we're on the first page no need to go through the @postid push
IF @pagenumber = 1
BEGIN
SET ROWCOUNT @perpage
SELECT ...
RETURN
END
-- otherwise
DECLARE @min_postid NUMERIC( 8, 0 ),
@position INT
SELECT @position = @perpage * ( @pagenumber - 1 ) + 1
SET ROWCOUNT @position
-- What happens here is it will select through the rows
-- and order the whole set.
-- It will stop push postid into @min_postid until it hits
-- ROWCOUNT and does this out of the ordered set (a work
-- table).
SELECT @min_postid = postid
FROM post
WHERE ...
ORDER BY postid ASC
SET ROWCOUNT @perpage
-- we know where we want to go (say the 28th post in a set of 50).
SELECT ...
FROM post
WHERE postid >= @min_postid
...
ORDER BY postid ASC
(Continued on next question...)
