In SQL, the Limit clause is used to restrict or control the number of records in the result set that is generated by the query. SQL by default displays the necessary number of records beginning at the top, but it also supports the use of the OFFSET keyword. You can use OFFSET to get the necessary number of result rows by starting from a custom row.

Syntax:

SELECT * FROM tablename LIMIT N;

SELECT * FROM tablename LIMIT N OFFSET offset;

LIMIT N: It’s used to restrict the number of records returned. N here starts at 0, but if you exceed LIMIT 0 ( then it does not return any record).

Example: a program to display only 2 records

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
host ='localhost',
database ='College',
user ='root',
)

cs = mydb.cursor()

# STUDENT and STudent are
# two different database
statement ="SELECT * FROM STUDENT LIMIT 2"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
	print(x)

Using OFFSET Keyword with LIMIT clause:

By using the OFFSET keyword in the LIMIT query, you can start from any position if you don’t want to start from the first. Let’s look at the corresponding code example:

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
host ='localhost',
database ='College',
user ='root',
)

cs = mydb.cursor()

# STUDENT and STudent are
# two different database
statement ="SELECT * FROM STUDENT LIMIT 2 OFFSET 1"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
	print(x)

In the code above, OFFSET 1 indicates that the resultset should start from the first row and LIMIT 2 indicates that only row 2 will be returned.

Note: The OFFSET keyword is used to specify the starting point. For example, if a query returned 100 rows of data and we have specified the OFFSET as 31, then data starting from the 52nd row till the 100th row will be returned.

Note: also read about Order by Clause – MySQL

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py

Rabecca Fatima

Staying up to the mark is what defines me. Hi all! I’m Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.