Select statement in SQLite recognizing row number

Question

I want to write SQLite statement something like this:

SELECT * FROM Table WHERE RowNumber BETWEEN 1 AND 10;

but i don't have such column RowNumber. I have primary key in my table. But is there row number by default that i could use ?

Also i am searching info about writing more complicated SQLite statement. So if you have some links in bookmarks please share.

Thanks.

Here is some additional information that helped me. In my case I was interested in an arbitrary set of rowid's. http://stackoverflow.com/questions/33512086/retrieve-sqlite-rows-by-rowid-efficiently/33512116#33512116 — M Katz, Nov 04 '15 at 04:42

score 32 · Accepted Answer · edited Mar 29 '18 at 05:29

32

You want to use LIMIT and OFFSET

SELECT * FROM Table LIMIT 10 OFFSET 0

Which can also be expressed with the following shorthand syntax

SELECT * FROM Table LIMIT X,Y

Where X represents the offset, which is exclusive, and Y represents the quantity, so for example

SELECT * FROM Table LIMIT 50,50

Would return rows 51-100

edited Mar 29 '18 at 05:29

oldboy

5,729
6
38
86

answered Jan 23 '12 at 18:52

Darren Kopp

76,581
9
79
93

2

just out of curiosity. Why do you need OFFSET 0? Isn't SELECT * FROM table LIMIT 10 enough? – Tooroop Dec 10 '12 at 09:50
You are correct, offset is optional if starting from 0. I likely just wrote it out of habit. – Darren Kopp Dec 10 '12 at 17:30

score 4 · Answer 2 · answered Jan 23 '12 at 18:51

The automatically-created rowid for a table can be accessed by a few different names. From the SQLite documentation:

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name.

score -1 · Answer 3 · answered Jan 23 '12 at 18:51

-1

SELECT * FROM Table WHERE ROWID BETWEEN 1 AND 10;

answered Jan 23 '12 at 18:51

StevieG

8,639
23
31

Selecting from rowid from 1 to 10 does not grant you that 10 rows will be returned, it does not guarantee that even 1 row will be returned. – kurast Jan 25 '12 at 13:59
6

Rowids are just guaranteed to be unique in sqlite, not guaranteed to be sequential. Only just after a vaccumm command they are sequentially adjusted from one. Most of the time what you proposed would work. But if I do: SELECT * FROM Table WHERE ROWID BETWEEN 1 AND 10; --returns ten lines DELETE FROM Table WHERE ROWID BETWEEN 1 AND 10; SELECT * FROM Table WHERE ROWID BETWEEN 1 AND 10; --returns zero lines, even if there are remaining lines ate the table. VACCUUM; --reorders the rowids SELECT * FROM Table WHERE ROWID BETWEEN 1 AND 10; --returns ten lines again – kurast Mar 06 '12 at 20:11

Select statement in SQLite recognizing row number

3 Answers3

Linked

Related