SQLite | Querying Data | LIMIT and OFFSET
LIMIT restricts the number of returned rows, and OFFSET selects the starting position.
Limit the number of rows
SELECT columns FROM table_name LIMIT row_count;
If the limit exceeds the number of stored rows, all available rows are returned.
create table user (id integer, name text, address text);
insert into user values (1, 'devkuma', 'Seoul');
insert into user values (2, 'kimkc', 'Busan');
insert into user values (3, 'araikuma', 'Paju');
insert into user values (4, 'happykuma', 'Paju');
insert into user values (5, 'mykuma', 'Daejeon');
insert into user values (6, 'yourkuma', 'Seongnam');
insert into user values (7, 'raccoon', 'Suwon');
insert into user values (8, 'noguri', 'Suwon');
select * from user limit 4;
The query returns rows 1 through 4.
Set the starting position with OFFSET
SELECT columns FROM table_name LIMIT row_count OFFSET offset;
SELECT columns FROM table_name LIMIT offset, row_count;
An offset of 4 skips four rows and starts with the fifth. OFFSET cannot be used without LIMIT.
select * from user limit 3 offset 4;
select * from user limit 4, 3;
Both queries return IDs 5, 6, and 7.
Combine ORDER BY and LIMIT
When both clauses are present, SQLite sorts first and then applies the limit.
select * from user order by address limit 5;
This returns the first five rows after sorting by address.