SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

SQL Limit query for a range of records in MySQL table

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.

  Download the SQL dump file for the student table here

Number of User Comments : 4


Google+

Rajkumar12-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 rawat08-08-2012
Hello Guys,
Show tables limit 4,4 not working
What will be command for limit in mysql
subhend08-08-2012
This is not for listing of tables of a database.
ankur08-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.
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer