Getting the first & Second highest number record from a table

Second Highest How to get the second highest mark of a class? We will use two sql commands limit and order by along with the select command to get the second highest record in a table. We will use our student table where each student mark is stored in a field. We will try to get the name and other details of a student who secured second highest mark.

  • Video Tutorial on SQL Second Highest Mark


Why second highest why not the first highest or the highest mark? That is because it is easy to get highest by using SQL Max command and using limit and order by also we can get the highest mark. After reading this tutorial you can generate that query or see the end of this tutorial to get the highest mark query.

Please check our sql limit and sql order by command before this.

By using order by command we can display the records in the order by marks. By adding the command DESC we can get the records starting from highest to lowest records. So we will get first record of the highest mark and then the second highest mark. We know we can restrict the number of display by using limit command so we will use this limit command to get one record starting from first. So this will return us the second record after the first record. That is if we add the limit command and start from 1 and ask to return 1 record then we will get the second highest mark student. ( not the first or the highest mark ).

Here is the sql command to get the second highest mark of class six students.
SELECT * FROM `student` WHERE class='Six' ORDER BY mark DESC LIMIT 1,1
Output is here
idnameclassmark
12ReckySix94
Limit command will pick up one record staring after the first record so we will get the second highest. If we want to get the first record or the highest mark student then we have to start from 0 record and display one. Like this
SELECT * FROM `student` where class='Six' ORDER BY mark desc LIMIT 0,1
idnameclassmark
33Kenn ReinSix96
You can see the only difference between above two SQL commands is the use of starting record mark in limit command. In first case it is 1 and in second case it is set to 0

Displaying top 3 records only

As you can see by using order by and limit command we can display records in different orders, now we will try to display the top three records from the top or first three highest marks. Here is the query.
SELECT * FROM `student` WHERE class='Six' ORDER BY mark DESC LIMIT 0,3
Output of this query is here.
idnameclassmark
33Kenn ReinSix96
12ReckySix94
11RonaldSix89
This way we can display any record, it can be fifth or 12th record from top. If we change the order by command to start in ascending order by changing the DESC to ASC ( or removing it , by default it is ASC ) we can get the lowest to highest and get the records from lowest ( last ) mark.
SELECT * FROM `student` where class = 'Six' 
ORDER BY mark ASC LIMIT 0,1
Try to find out the second lowest mark of class six now

Here it is
idnameclassmark
17TumyuSix54

PHP Code

You can use the same PHP code used for SELECT query by replacing SQL part only.


SQL Math References Maximum value Query
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    gaurav singh kushwaha

    16-12-2009

    select sal, count, rownum from (select sal , count(sal) count from salary group by sal order by sal desc) where rownum in (1,2,3)
    haridas

    12-03-2010

    Thank you for this post. Really helpful and easy to understand.
    senthil

    24-05-2010

    i count each product sold by using group by and count and i have to find maximum among them.i have tried with group by clause.but i can't
    Mayank Singh Parmar

    29-06-2010

    Very well written. Thanks for providing the student table.
    velu

    25-09-2010

    the table data is id name price date 1 acc 10.10 24-SEP-2010 2 acc 15.10 26-SEP-2010 3 acc 13.10 27-SEP-2010 i want result as follows display id, max(price),date format in dd/mm/yyyy my price field is varchar
    velu

    25-09-2010

    how to convert varchar values to int values
    shani

    27-11-2010

    i m stuck in this situation. kindly some one help me. if the person have 16000 thousand amount in his account. and that person have to pay 14000 thousand to someone. and the result will be 2000 and next time when he will deposit his 4000 thousand then result will 4000 + 2000. how can i do this? thanx in advance
    rashmi

    22-01-2011

    if I have a colum as sales_date,which has data type as date & default value as 2010-01-01(i.e 1st jan 2010).When I update rest of the data in next record, I want the date should be increamented in that record. Ex- 2nd record should have date as 2010-01-02 i.e 2 jan 2010. What could be the function to increament the date in next row?
    jeet singh parmar

    08-04-2011

    select * from tab_name where col1 = (select MAX(col1) from tab_name where col2 = value
    raghavan

    22-10-2011

    how to add column values of a single record. ID s1 s2 s3 1 20 29 28 2 78 98 09 . I wanna add two highest col values of a single record
    gary

    23-10-2011

    hey, i have a list of employees with various information in each recording including birthdate. can someone tell me how to write a single query that will return the oldest and youngest employee?
    jeya

    07-01-2012

    select max(sal) from emp where sal <(select max(sal) from emp); whether is this right or wrong for find second maximum salary in a table of content?
    SUNIL KUMAR

    04-09-2013

    Hi, I need a sql query:- My Requirement is as follows:-

    I am working on a project which will take input from the user, & based on the inputs it will give the records.

    For Eg:- I need to generate 5 sets of 20 questions . Each set of questions will contain 5 Java Ques, 8 DataBase, 5 .Net, 2 Subjective Questions. So, i am able to get the data from table all the 100 ques(5 X 20). Now before sending it to the user , i need to order the data .
    The conditions are as follows:-

    First 20 questions will belong to set 1, next 20 questions will belong to set2 and so on .....
    So, i need to shuffle the data so that every 20 questions from top will contain 5 Java Ques, 8 DataBase, 5 .Net, 2 Subjective Questions. And the questions should be in arranged in random order. i.e. if the 1st ques is from JAVA , then 2nd ques should not be from JAVA, it should be from other subject. So, two subjects should not be together.
    jeyashree

    10-10-2013

    Hi I need a sql query. My requirement is as follows

    I have a table containing the three subject marks of a student column named as maths,physics, chemistry
    From that three columns,I need to find the highest and lowest marks of a student and I want a query to Display the Student's Highest and Lowest marks along with the subjects.

    Can anyone help me to complete this task
    kiran

    22-03-2014

    how to fetch data from database whan select start date to end date and minumum price to maximum price than show the data
    smo

    21-04-2014

    You can sue start date and end date by using date between query , then add AND to it and give maximum price and minimum price.
    SELECT * FROM dt_tb WHERE dt BETWEEN '2014-4-2' AND '2014-4-18' and price BETWEEN max_price and min_price
    swamy

    20-07-2014

    How to get the firstday and lastday salary difference
    supreetha

    24-08-2014

    hi..i m doing mini project of internal mark calculation...i finished half of the project...but calculation part is remaining...will u pls help me!!!!the concept behind this project is...i have unit test1,2,3 and model exam1 from these i have to add best 3 examination marks with assignment and innovative mark...pls help me!!!
    Peter

    31-08-2014

    How does one get the highest (or last) numerical value from a column called ID in a table called PROPERTY
    Edeki Michael

    29-12-2014

    I WANT TO DISPLAY RANK OF STUDENTS ACCORDING TO THEIR SCORES AND BRING OUT THE 1ST, 2ND, 3RD.......POSITIONS
    RESPECTIVELY MS-ACCESS
    sugan kannan

    13-01-2015

    I want to display rank of student according to their CGPA and also wand to give if 2 student get same CGPA then i have to give same rank to both...
    Ujjwal kumar

    09-11-2015

    HOW TO RETRIEVE HIGEST VALUES FROM 1000 NUMBER

    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-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer