How to find highest and second highest record or column value in sql

MySQL: In mysql to get the highest and second highest column or record we can use order by and limit

For example : The below query will give me the highest record:

SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1

To get the second highest record:

SELECT * FROM `student` where class=’tenth’ ORDER BY mark desc LIMIT 0,1

To get highest record or second highest record in Oracle we can use ROWNUM . Oracle doesn’t have LIMIT and ROWNUM works in the same way as LIMIT works in MYSQL.

Below query will give me the highest or the max student record with highest marks
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 1 ORDER BY marks

Second highest record can be get through below command
SELECT * FROM `student` where class=’tenth’ and ROWNUM == 2 ORDER BY marks

Tedious way would be to write nested sql query to find the second highest record

SELECT score,player FROM scores WHERE score=(SELECT max(score) FROM scores WHERE score< (SELECT max(score) FROM scores));
The above finds the second highest score in the scores table and the name of the player.
Reading from right to left:
1. The first select finds the highest score.
2. The second select find the highest from the rest.
3. The last select finds the name of the player.

Tags: second highest record