paginationsybaserowcount

How can I do paging in Sybase without making temp table? (oracle rownum issue)

In Oracle, usually query like this for paging.

SELECT * FROM (SELECT *, rownum rid  FROM TABLEA WHERE rownum <= #pageend#)
WHERE rid > #pagestart#

However, there is no "rownum" function in Sybase DBMS.

How can I do that query exactlly same in Sybase?

I found some ways.

  1. use "rowcount"

    set rowcount 10

    select * from TABLEA

  2. use identity (make temp table)

    SELECT *, ROWNUM=IDENTITY(8) INTO #TEMP FROM TABLEA

    SELECT * FROM #TEMP WHERE ROWNUM < #pageend# AND ROWNUM >= #pagestart#

    DROP TABLE #TEMP

these are not what I want.

rowcount is set at the session level and i don't want make temp table.

Solution

  • If you have a unique id column on your table you could use SELECT TOP n

    SELECT TOP 10 *
FROM tableA
WHERE id BETWEEN @start AND @end