3

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?

Jonathan Leffler
  • 666,971
  • 126
  • 813
  • 1,185

4 Answers4

13

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
grantc
  • 1,693
  • 12
  • 13
4

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: How to limit result set size for arbitrary query in Ingres?

Community
  • 1
  • 1
Adrian
  • 5,571
  • 9
  • 41
  • 68
  • Thanks Adrian. we are upgrading to Ingres9.2 next year so i will be able to to amke it work then. –  Oct 12 '09 at 11:36
2
Select top 1000 * from table;

works perfectly in 10.0

gdrt
  • 2,563
  • 3
  • 32
  • 51
Andrey
  • 1,331
  • 10
  • 29
  • 57
0

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
Grisha Weintraub
  • 7,435
  • 1
  • 21
  • 43
gion_13
  • 9
  • 1
  • 2
    Ingres does not have a rownum column. There is a hidden tid column however the value contained is based on the page number and it's position within the page. – grantc Feb 22 '10 at 10:32