SQL Limit query for a range of records in MySQL table


SQL Limit 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  position of record and number of records. We will pass this starting and number of records 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.

LIMIT MySQL query to restrict number of records returned from database 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
idnameclassmark
21Babby JohnFour69
22ReggidSeven55
23HerodEight79
24Tiddy NowSeven78
25Giff TowSeven88
26CreleaSeven79
27Big NoseThree81
28Rojj BaseSeven86
29Tess PlayedSeven55
30Reppy RedSix79
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.
idnameclassmark
23HerodEight79
7My John RobFive78
18HonnyFive75
8AsruidFive85
31Marry ToeeyFour88
21Babby JohnFour69
1John DeoFour75
16GimmyFour88
15Tade RowFour88
10Big JohnFour55

LIMIT with WHERE condition and order by Query

Read more on WHERE to filter records based matching Conditions

List of students with Mark above 80 ( ten records only by using LIMIT )
SELECT * FROM `student` WHERE mark >80 LIMIT 0,10
List of students with Mark above 80 ( ten records only ) in the order of lowest to highest.
SELECT * FROM `student` WHERE mark >80 ORDER BY mark LIMIT 0,10
In the order of Highest to lowest
SELECT * FROM `student` WHERE mark >80 ORDER BY mark DESC LIMIT 0,10

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

Selecting records Collecting data by linking more than one table

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Rajkumar

    12-09-2011

    I am just looking for an equivalent to top clause in MS SQL SERVER 2005 that follows where clause (something similar to Limit in Mysql). I require this to be used in another application to retriev the latest record from DB. The clause that comes next to select cant be used. Can you please help me.
    Anil kumar rawat

    08-08-2012

    Hello Guys, Show tables limit 4,4 not working What will be command for limit in mysql
    subhend

    08-08-2012

    This is not for listing of tables of a database.
    ankur

    08-04-2014

    Helo Guys,
    i have a table, table name is "employees" and i have insrt duplicates values more than two times. now i want remove that values but want to put one value only waht i do in sql server query.
    Shahbaz Ahmed Bhatti

    12-08-2015

    Plus2net was my first tutorial website for learning php 8 years ago, now i again need to check query an di found this website again
    very gooooooooood healp
    cheers plus2net team
    sheddie

    26-02-2018

    how can you display all the marks below 80 on the webpage or any other limit?
    I.e query to show data in a table from a database given a limit of actual data in the database
    smo1234

    14-02-2019

    SELECT * FROM `student` WHERE mark < 80

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer