sql select data in row number

[eml]

Hi,

Is there any way to code sql to select a single row by index number WITHOUT looping through every record.

E.g: if I have a database of 1000 records, is there any way I can print record 556 without knowing anything about that row other than its index position within the datbase?



Thanks

[Larz60+]

That depends on the database engine.
Most have a medthod that allows selecting by rowid, but
be aware that that rowid is not immutable and can change at any moment,

[buran]

well, this is over-simplified...
is it just one table, because with multiple tables, JOINs between, etc.... it's not that easy to index the row. by design you may have some auto-increment id field that usually will serve as a primary key, but it's may be missing certain values (e.g. after deletion of records, etc.)
with simple small table like 1000 records you may execute SELECT * FROM TABLE and then fetchall()
this will result in a tuple of tuples and you may retrieve record with index 555 (index is zero-based as you know)
same apply for a more complex detabase, but index will depend on SORT BY clause

EDIT: Of course, Larz60+ advice about rowid (if available) is much more relevant :-)

[eml]

That depends on the database engine.
Most have a medthod that allows selecting by rowid, but
be aware that that rowid is not immutable and can change at any moment,

I'm using sqlite3

---

well, this is over-simplified...
is it just one table, because with multiple tables, JOINs between, etc.... it's not that easy to index the row. by design you may have some auto-increment id field that usually will serve as a primary key, but it's may be missing certain values (e.g. after deletion of records, etc.)
with simple small table like 1000 records you may execute SELECT * FROM TABLE and then fetchall()
this will result in a tuple of tuples and you may retrieve record with index 555 (index is zero-based as you know)
same apply for a more complex detabase, but index will depend on SORT BY clause

EDIT: Of course, Larz60+ advice about rowid (if available) is much more relevant :-)

Thanks

[Larz60+]

DSo here's an example of why you cannot rely on it

Output:
sqlite> Create table TEMP1 ('First' text, 'Last' text);
sqlite> .schema TEMP1
CREATE TABLE TEMP1 ('First' text, 'Last' text);
sqlite> insert into temp1 values('John', 'Smith');
sqlite> insert into temp1 values('Fred', 'Fuller');
sqlite> insert into temp1 values('Jack', 'Rabbit');
sqlite> select rowid from TEMP1;
1
2
3
sqlite> DELETE from TEMP1 where rowid == 2;
sqlite> select rowid from TEMP1;
1
3
sqlite> select rowid, * from TEMP1;
1|John|Smith
3|Jack|Rabbit
sqlite> VACUUM;
sqlite> select rowid, * from TEMP1;
1|John|Smith
2|Jack|Rabbit
sqlite>