How To Limit The Number Of Rows Returned In SQLite

SQLite is a lightweight and self-contained relational database management system in short RDBMS. It supports standard SQL syntax. It is designed as a simple and easy-to-use database. It requires very less configurations which makes it very easy for developers to integrate it into any application. In this article, we are going to explore the topic “how to limit the number of rows returned in SQLite“. We are going to explore its basic concepts with some clear and concise examples.

How to Limit The Number Of Rows Returned

In SQLite, we can use the LIMIT clause to limit the number of rows returned. With the help of the LIMIT clause, we can restrict the number of rows returned by the query at a moment. We can specify how many rows we want to fetch at a moment. We can occasionally use the OFFSET clause too. OFFSET clause will help us to skip a certain number of rows, we do not want to display at that moment.

Syntax:

SELECT *

FROM table_name

LIMIT (Specify number of rows);

Setting Up a Table in the Database

Before moving to some examples, we need to create a table in our database first. We will need a table in our database in order to perform some operations on it.

Table : geeksforgeeks

Creating the table

CREATE TABLE geeksforgeeks (
    id INTEGER PRIMARY KEy,
    name TEXT,
      course TEXT,
    potd_streak INTEGER
);

Adding data to our table and displaying it

--adding data

INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(101,'Vishu','Python',280); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(102,'Aayush','Java',250); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(103,'Neeraj','Python',180); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(104,'Sumit','JavaScript',190); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(105,'Vivek','Java',100); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(106,'Harsh','Python',50); 
INSERT INTO geeksforgeeks(id, name, course, potd_streak)
VALUES(107,'Amit','C++',220); 

--displaying our table's data

SELECT * From geeksforgeeks;

Output:

Table geeksforgeeks

Now we are ready as we have done with creating the table.

Example 1: LIMIT Clause Without Using WHERE Clause

In this example, we are going to return a limited number of rows. For this purpose we are going to use LIMIT clause to limit the the number of rows returned.

Query:

SELECT *
From geeksforgeeks
LIMIT 5;

Output:

Limit Without using Where Clause

Explanation: In the above example, we have used LIMIT 5. This will only return us the first 5 records. As seen in the image, we have only got the first 5 records. If we want to display more like 10, 20, then we have to specify this accordingly.

Example 2: LIMIT Clause with Using WHERE Clause

Unlike previous example, we are going to display some specific records only. We will display some specific number of records with the help of LIMIT and WHERE clause.

Query:

SELECT *
From geeksforgeeks
WHERE course = 'Python'
LIMIT 2;

Output:

Limit Clause With Using Where Clause

Explanation : In this example, we have specified that we only want those records where course column has ‘Python’ in it. We also specified that, we only want to display first 2 records. As we can see in the above image, there are only two records with ‘Python’ in there course column.

Example 3: LIMIT Clause Along with OFFSET Clause

In this example, we are going to use limit clause along with the offset clause. We are going to discuss two case related to it.

CASE 1: Displaying 5 Records Skipping the First 2 Rows

Query:

SELECT *
From geeksforgeeks
LIMIT 5 OFFSET 2;

Output:

displaying 5 records skipping first 2

Explanation: In this case, we have specified in query to display only five records. With the help of offset clause we can skip the first 2 rows. As we can observe, id 102, 103 are not displayed as they are first 2 records and succeeding 5 records are displayed.

CASE 2: Displaying Records From 4 to 7

Query:

SELECT *
From geeksforgeeks
LIMIT 4 OFFSET 3;

Output:

Displaying records 4-7

Explanation: We have specified in the query to skip first 3 records. We have also set the limit up to 4 records. Therefore as a result we will get all the records which lie between 4-7 (including 4-7).

Conclusion

SQLite is a light-weighted relational database management system. It is designed as simple and ease to use data base. It supports standard SQL syntax. With the help of LIMIT clause we can restrict or define the desired number of rows we want to display. We can occasionally use OFFSET along with it. We have overall covered all the real life examples on using LIMIT clause. We have also seen how we can implement LIMIT clause along with offset clause. Now you have a good understanding of LIMIT clause, you you write your desired query related to LIMIT clause and can get your desired output.