SQL Limit Query for start & end for range of records in MySQL

We may require to display some part of the records returned by a query specifying a range. What is the range to be returned we can specify by saying the starting and ending record number. We will pass this starting and ending number along with the SQL LIMIT command to restrict the records within that range. We will see the syntax of this query in our student MySQL table.

SELECT * FROM `student` LIMIT 0, 10

We have specified here to return 10 records starting from 0 or from the first record. Same way we can ask for 10 records starting from 20th record like this

SELECT * FROM `student` LIMIT 20, 10

This will return 10 records from 21st record. That is from 21st record to 30th record. Here is the output

id	name	class	mark
21	Babby John	Four	69
22	Reggid	Seven	55
23	Herod	Eight	79
24	Tiddy Now	Seven	78
25	Giff Tow	Seven	88
26	Crelea	Seven	79
27	Big Nose	Three	81
28	Rojj Base	Seven	86
29	Tess Played	Seven	55
30	Reppy Red	Six	79

In MSSQL we can use TOP query to display required number of records, similarly in Oracle we use ROWNUM to collect required number of records.

Limit with order by query

Limit query along with Order By query will give us power full tool to develop applications. Let us try a simple limit query along with order by

SELECT * FROM `student`  ORDER BY class limit 0,10

In the above example the 10 records will be displayed based on alphabetical order of class column. Some sample records are here.

id	name	class	mark
23	Herod	Eight	79
7	My John Rob	Five	78
18	Honny	Five	75
8	Asruid	Five	85
31	Marry Toeey	Four	88
21	Babby John	Four	69
1	John Deo	Four	75
16	Gimmy	Four	88
15	Tade Row	Four	88
10	Big John	Four	55

Recently added five records of a table.

If your table have auto increment unique id or time stamp field then you can display recently added records like this.

SELECT * FROM `student`  ORDER BY id DESC limit 0,5

Here id is an auto incremented unique field.

If you have time stamp then you can use like this

SELECT * from student ORDER BY dt DESC limit 0,5

Here dt is time stamp or date and time field.

Read how Order by command with LIMIT is used to display highest 3 records of student table

Paging script in PHP MySQL

This is quite useful for designing paging in any script. Paging is known as displaying records in page wise with fixed number of records per page. There will be navigational menu to move between any pages and go to next and previous pages. The best example of php paging is the way google display search results. It display an easy navigational menu at the bottom of each search result page to go to next or previous page or any other page.

Note that this is returning number of records and this has no connection with the id number field used here. If the ID numbers are different then also the query will return 10 records starting from 21 record ( irrespective of the ID numbers ). If you want records with particular ID range specified then you have to use sql between command.

PHP Script using LIMIT Query

You can read more on mysqli connection string here.


require "config.php";// Database connection
//////////////////////////////
if($stmt = $connection->query("SELECT id, name ,class, mark FROM student limit 0,5")){
 echo "No of records : ".$stmt->num_rows."<br>";
echo "<table class='table table-striped'>
<tr class='info'> <th> ID</th><th>Name</th><th>Class</th><th>Mark</th></tr>";
while ($row = $stmt->fetch_assoc()) {
  echo "<tr><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark] </td></tr>";
 }
echo "</table>";
}else{
echo $connection->error;
}
exit;

Download the SQL dump file for the student table here

SQL Limit query for a range of records in MySQL table

Limit with order by query

Recently added five records of a table.

Paging script in PHP MySQL

PHP Script using LIMIT Query

SQL Popular Tutorials

SQL Limit query for a range of records in MySQL table

Limit with order by query

Recently added five records of a table.

Paging script in PHP MySQL

PHP Script using LIMIT Query

Subscribe to our mailing list

Subscribe to our mailing list

SQL Popular Tutorials