SQLite: limit clause in select statements

With the limit clause, SQLite can be instructed to return only the first n records or the records n through m (paging).

Test data

In order to demonstrate this, some test data is necessary.

The table is filled with the numbers 0 through 9 and the respective english representation.

The table is not filled in any particualar order on purpose.

create table nums (
  num integer,
  txt text
);

insert into nums values (5, 'five'  );
insert into nums values (9, 'nine'  );
insert into nums values (7, 'seven' );
insert into nums values (1, 'one'   );
insert into nums values (4, 'four'  );
insert into nums values (8, 'eight' );
insert into nums values (0, 'zero'  );
insert into nums values (2, 'two'   );
insert into nums values (6, 'six'   );
insert into nums values (3, 'three' );

Github repository about-sqlite, path: /sql/select/limit/data.sql

Selecting the first 4 records

In the following select statement, the limit clause is used to limit the result set to 4 records only:

select
  num,
  txt
from
  nums
order by
  num
limit 4
;
--
-- 0|zero
-- 1|one
-- 2|two
-- 3|three

Github repository about-sqlite, path: /sql/select/limit/first-4-records.sql

Select the next 2 records (paging)

If the limit clause is given two arguments, the first is interpreted as the start record and the second argument as the number of records that should be returned:

select
  num,
  txt
from
  nums
order by
  num
limit 4, 2 -- 4: Start record, 2: how many records
;
--
-- 4|four
-- 5|five

Github repository about-sqlite, path: /sql/select/limit/next-2-records.sql

SQLite also understands the limit … offset clause which is easier to read. The next query returns the same records:

select
  num,
  txt
from
  nums
order by
  num
limit  2
offset 4
;
--
-- 4|four
-- 5|five

Github repository about-sqlite, path: /sql/select/limit/offset.sql

Cleaning up

Dropping the table to clean up.

drop table nums;

Github repository about-sqlite, path: /sql/select/limit/cleanup.sql

SQLite: limit clause in select statements

Test data

Selecting the first 4 records

Select the next 2 records (paging)

Cleaning up

See also