How to Limit The Amount of Results Returned in Sybase

How do I limit the amount of results returned in Sybase?

I believe you can do a SET ROWCOUNT 10 first, then all queries in this session until a further SET ROWCOUNT will return no more than 10 rows. As a comment points out, this affects all following queries in the session (not just SELECTs!) until turned off (by setting to 0) or set differently -- this "global" effect makes it less handy than the typical LIMIT clause of other engines, which is inherently per-query, but, I don't think you can do anything about that.

Using LIMIT clause in Sybase

I'm not sure which sybase do you use, but this:

SELECT TOP 5 * FROM Employees ORDER BY Surname

will work on ASE.

You can also try this way:

SELECT TOP(5) * FROM Employees ORDER BY Surname;

Correct Syntax When Limiting Results in Sybase SQL Statement Query

The * is wrong. Just lose it and you should be OK:

SELECT top 10 -- * removed here
c.claim_problem as problem,
-- etc.

limiting results in sybase ASE between a particular range

Add TOP numberofrecords you want to return

DECLARE @intStartRow int;
DECLARE @intEndRow int;

SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;    

 WITH wl_eval AS
(SELECT field, 
 ROW_NUMBER() OVER(ORDER BY intID DESC) as intRow, 
 COUNT(intID) OVER() AS intTotalHits 
     FROM tblBlog)
 SELECT field, intTotalHits FROM wl
 WHERE intRow BETWEEN @intStartRow AND @intEndRow

Sybase offset for pagination

Quoting from http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12:

Sybase does not have a direct equivalent to Oracle's rownum but its functionality can be emulated in a lot of cases.

You can set a maximum rowcount, which will limit the number of rows returned by any particular query:

set rowcount 150

That limit will apply until it is reset:

set rowcount 0

You could select into a temporary table, then pull data from that:

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

You could optimize storage on the temp table by storing only ID columns, which you then join back to the original table for your select.

The FAQ also suggests other solutions, including cursors or Sybperl.

Sybase LIST function truncating results after 256 characters

Try this:

SELECT
    LIST( CAST( myVal AS nvarchar(max) ), ',' )
FROM
    myTable

How do I limit the number of records returned for Interbase 7.1?

I think I figured it out. Needed to do something like this...

SELECT * FROM table ORDER BY col ROWS 1

---

Related Topics