Paging in Pervasive SQL

Question

How to do paging in Pervasive SQL (version 9.1)? I need to do something similar like:

//MySQL
SELECT foo FROM table LIMIT 10, 10

But I can't find a way to define offset.

Answer 1

I face this problem in MS Sql too... no Limit or rownumber functions. What I do is insert the keys for my final query result (or sometimes the entire list of fields) into a temp table with an identity column... then I delete from the temp table everything outside the range I want... then use a join against the keys and the original table, to bring back the items I want. This works if you have a nice unique key - if you don't, well... that's a design problem in itself.

Alternative with slightly better performance is to skip the deleting step and just use the row numbers in your final join. Another performance improvement is to use the TOP operator so that at the very least, you don't have to grab the stuff past the end of what you want.

So... in pseudo-code... to grab items 80-89...

create table #keys (rownum int identity(1,1), key varchar(10))

insert #keys (key)
select TOP 89 key from myTable ORDER BY whatever

delete #keys where rownumber < 80

select <columns> from #keys join myTable on #keys.key = myTable.key

Answer 2

Our paging required that we be able to pass in the current page number and page size (along with some additional filter parameters) as variables. Since a select top @page_size doesn't work in MS SQL, we came up with creating an temporary or variable table to assign each rows primary key an identity that can later be filtered on for the desired page number and size.

** Note that if you have a GUID primary key or a compound key, you just have to change the object id on the temporary table to a uniqueidentifier or add the additional key columns to the table.

The down side to this is that it still has to insert all of the results into the temporary table, but at least it is only the keys. This works in MS SQL, but should be able to work for any DB with minimal tweaks.

declare @page_number int, @page_size int -- add any additional search parameters here

--create the temporary table with the identity column and the id
--of the record that you'll be selecting. This is an in memory
--table, so if the number of rows you'll be inserting is greater
--than 10,000, then you should use a temporary table in tempdb
--instead. To do this, use
--CREATE TABLE #temp_table (row_num int IDENTITY(1,1), objectid int)
--and change all the references to @temp_table to #temp_table
DECLARE @temp_table TABLE (row_num int IDENTITY(1,1), objectid int)

--insert into the temporary table with the ids of the records
--we want to return. It's critical to make sure the order by
--reflects the order of the records to return so that the row_num
--values are set in the correct order and we are selecting the
--correct records based on the page
INSERT INTO @temp_table (objectid)

/* Example: Select that inserts records into the temporary table
SELECT personid
FROM person WITH (NOLOCK)
inner join degree WITH (NOLOCK) on degree.personid = person.personid
WHERE person.lastname = @last_name
ORDER BY person.lastname asc, person.firsname asc
*/

--get the total number of rows that we matched
DECLARE @total_rows int
SET @total_rows = @@ROWCOUNT
--calculate the total number of pages based on the number of
--rows that matched and the page size passed in as a parameter
DECLARE @total_pages int
--add the @page_size - 1 to the total number of rows to
--calculate the total number of pages. This is because sql
--alwasy rounds down for division of integers
SET @total_pages = (@total_rows + @page_size - 1) / @page_size

--return the result set we are interested in by joining
--back to the @temp_table and filtering by row_num
/* Example: Selecting the data to return. If the insert was done
properly, then you should always be joining the table that contains
the rows to return to the objectid column on the @temp_table

SELECT person.*
FROM person WITH (NOLOCK) INNER JOIN @temp_table tt
ON person.personid = tt.objectid
*/
--return only the rows in the page that we are interested in
--and order by the row_num column of the @temp_table to make sure
--we are selecting the correct records
WHERE tt.row_num < (@page_size * @page_number) + 1
AND tt.row_num > (@page_size * @page_number) - @page_size
ORDER BY tt.row_num

Answer 3

I ended up doing the paging in code. I just skip the first records in loop.

I thought I made up an easy way for doing the paging, but it seems that pervasive sql doesn't allow order clauses in subqueries. But this should work on other DBs (I tested it on firebird)

select *
from (select top [rows] * from
(select top [rows * pagenumber] * from mytable order by id)
order by id desc)
order by id

Answer 4

Tested query in PSQL:

select top n * from tablename where id not in( select top k id from tablename )

for all n = no.of records u need to fetch at a time. and k = multiples of n(eg. n=5; k=0,5,10,15,....)