Restrict number of rows retrieved in Ingres select query

Question

I want to know if there is a way to select a subsequent bunch of rows in select query in Ingres. For example, the first 500 rows can be selected by using the select first 500 from tablename, but if I want to select rows 501 to 1000, is there any way to do that?

Answer 1

You can use the OFFSET clause in the following way:

SELECT col_name 
FROM table 
WHERE col_name = 'value' 
OFFSET m FETCH FIRST n ROWS ONLY

For example

SELECT *
FROM table  
OFFSET 501 FETCH FIRST 500 ROWS ONLY

Answer 2

Thanks grant, What i gather from the ingres.com site is that the 'OFFSET' has been introduced only in Ingres9.2 .My client currently uses Ingres2006 so it does not support the 'OFFSET' tag. Is there another way around?

Regards, Rashmi

Answer 3

You can use

SELECT FIRST 10 * FROM table or SELECT FIRST 10 field1, field2 FROM table

I tested it in Ingres 9.2 and it works

See also: http://stackoverflow.com/questions/49602/how-to-limit-result-set-size-for-arbitrary-query-in-ingres

Answer 4

you can try restricting the row number using the "rownum" var:

SELECT * from TABLE

WHERE rownum <=500

i haven't tested it on your problem, but it should work fine :

SELECT * from TABLE

WHERE rownum >500

AND rownum <= 1000