Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Get n number of highest and lowest records in MySQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 256
    Comment on it

    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.


    idnamemarks
    1Amit64
    2Rajiv52
    3Deepak80
    4Ankit78
    5Mohit45
    6Ram97

    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
        )
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: