Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data.

As far as I can observe, inserted data to a table is stored in no order but is retrieved by identical SELECT statements in the same order.

I could get use of 100 random rows, but random rows are not useful because I always need to retrieve the same data from the database in order to compare results of the algorithm I'm developing.

I don't want to order data by any field because otherwise I'd be biasing the selected data.

The sql statement should be something like:

SELECT field1, field2 FROM table1 WHERE row_number > 70000 LIMIT 100;

or probably even better (as I think LIMIT doesn't prevent the database from outputting every record above row number 70000):

SELECT field1, field2 FROM table1 WHERE row_number > 70000 AND row_number <= 70100;

What SELECT statement should I use?

share|improve this question
1  
Without ordering, you can never be sure that you get the same 100 rows. If you want to get them in physical order and the rows don't get updated or deleted, you can play with the ctid column. Bye the way, why do you need this? –  dezso Nov 7 '12 at 14:49
    
@dezso: rows don't ever get updated or deleted. It's enough for this case to get them in physical order. –  Luis Nov 7 '12 at 14:53

1 Answer 1

up vote 3 down vote accepted

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.