SQLite Limit clause

SQLite is the most popular and most used database engine which is written in c programming language. It is serverless and self-contained and used for developing embedded software devices like TVs, Mobile Phones, etc. In this article, we will be learning about the SQLite Limit Clause using examples simply and understandably. How does the SQLite limit Clause work, with the order by and pagination?

SQLite Limit Clause

We use SQLite Limit Clause to retrieve the data that we want, which means we are going to just use the LIMIT VALUE to fetch the limited data. The limit value is nothing but a number that specifies how many records you want to retrieve.

• The main advantage of the Limit Clause is that it optimizes the SQLite query.
• SQLiteorder specifies LIMIT Clause is used mostly on large tables so that we can retrieve the required data from the large number of records.
• The limit value must be a positive integer.

Syntax:

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

Explanation:

Limit Clause is always used with the select statement as we can only fetch the data using the select statement followed by the column names or the table names and the comes the LIMIT Clause . Now let us see the example for better understanding.

Example of LIMIT Clause

To understand the Limit Clause in depth we need a table on which we will perform operations. So we have created a table called faculty and it consists of Id,name, salary and dept as Columns. If you don’t know How to Create a Table in SQLite then refer this.

After Inserting some data in faculty table, Table looks like:

Faculty Table

Simple LIMIT Clause

We are going to use the Limit Clause on the single column by using the below syntax.

Syntax:

SELECT name
FROM faculty
limit 3;

Output:

Explanation: We have used the select statement to fetch the data followed by column name for which we are going to apply the limit clause and followed by the limit value. As, we have used Limit Clause on the name column, the name column is fetched and as the limit value is 3, three records are retrieved.

LIMIT Clause With Multiple Columns

We need to just seperate the two different columns by using comma( , ). Now let us try to apply limit on the multiple columns by using the below syntax.

SELECT salary, dept 
FROM 
faculty limit 3;

Output:

Explanation: In the above Query, We have fetch the data of more than one column (salary, dept) from the faculty table and apply the limit condition of 3.

LIMIT Clause With ORDER BY Clause

Now we are going to use LIMIT Clause with ORDER BY. ORDER BY clause is used to give the result set in an order and now it is going to work with the limit Clause and accordingly the result set is displayed.

SELECT expressions
FROM tables
[WHERE CONDITIONS]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows 

Explanation:

Select statement followed by the column names then comes the table name from which you are going to fetch the data and the followed by the where condition, Order by Clause and the Limt Clause, where you use the limit value.

Query:

SELECT name
FROM faculty
WHERE salary >= 25000
ORDER BY name desc
limit 2;

Output:

Explanation: Actually, In our table you can see 4 faculties whose salary is greater than 25000, but as we have set the limit value as 2 and we have also used order by Clause to display the names in the descending order thus, there are only 2 names displayed as shown above.

PAGINATION

Now we are going to see Limit Clause with the pagination that means limit Clause is used along with the OFFSET Clause.

OFFSET Clause is used to let know the select statement from which row it is to start fetching the data whereas LIMIT Clause is used to fetch the data from the starting row and fetches upto the limit value that has been specified.

One thing to say that if LIMIT VALUE is the number that specifies how many records to specify where as OFFSET is nothing but the starting point from which record the output is to be displayed.

Syntax:

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

Query:

SELECT * FROM faculty
LIMIT 3 OFFSET 2;

Explanation:

We are using select statement followed by the column name and from the table name that you are fetching from and then followed by LIMIT CLAUSE (value) and OFFSET Clause (row number).
Output:

Explanation: In the above Query, We have fetched the 3 record which are of 3rd, 4th and 5th row as we have used offset value as 2. In the same way if we use offset value as 3 the output is going to display from below the 3rd row and stops if you specify the limit value else it fetches up to the bottom of the table.

Conclusion

After reading overall article we can say that the SQLite Limit Clause is used to optimize the SQLite query and it makes us to retrieve the data in very less time. The main usage of SQLite is to retrieve only the neccessary and limited data at that point of time.