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
  • 730,956
  • 141
  • 904
  • 1,278

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,703
  • 12
  • 13
5

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
  • 6,013
  • 10
  • 47
  • 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
  • 3,160
  • 4
  • 37
  • 56
Andrey
  • 1,629
  • 13
  • 37
  • 65
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,803
  • 1
  • 25
  • 45
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