In my previous blog How to use Limit clause in MySQL Query, there is explanation of LIMIT clause and its use with SELECT statement. This article explains the use of LIMIT with ORDER BY clause to get 'n' number of Highest and Lowest records in MySQL.

Syntax :

SELECT columns
FROM table
ORDER BY ASC | DESC 
LIMIT count;

Example : Suppose we have a table student_marks in database which contains the marks of each student. How we can get records of 3 students who got highest and lowest marks.

1.Get records of 3 students with highest marks :

$query = mysql_query("SELECT * FROM `student_marks` ORDER BY `marks` DESC LIMIT 3");
while($result = mysql_fetch_assoc($query))
{
 echo "<pre>";
 print_r($result);
 echo "</pre>";
}

In the above query the records are arranged in the descending order of marks and top 3 records are returned.

Output :

Array
(
    [id] => 6
    [name] => RAM
    [marks] => 97
)

Array
(
     [id] => 3
    [name] => Deepak
    [marks] => 80
)

Array
(
    [id] => 4
    [name] => Ankit
    [marks] => 78
)

2. Get records of 3 students with lowest marks :

$query = mysql_query("SELECT * FROM `student_marks` ORDER BY `marks` LIMIT 3");
while($result = mysql_fetch_assoc($query))
{
  echo "<pre>";
  print_r($result);
  echo "</pre>";
}

Output :

Array
    (
        [id] => 5
        [name] => Mohit
        [marks] => 45
    )

    Array
    (
         [id] => 2
        [name] => Rajiv
        [marks] => 52
    )

    Array
    (
        [id] => 1
        [name] => Amit
        [marks] => 64
    )