Getting first, second highest & last record of table

Getting the first & Second highest number record from a table

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.

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

id	name	class	mark
12	Recky	Six	94

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

id	name	class	mark
33	Kenn Rein	Six	96

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 recordes 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.

id	name	class	mark
33	Kenn Rein	Six	96
12	Recky	Six	94
11	Ronald	Six	89

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

id	name	class	mark
17	Tumyu	Six	54

PHP Code

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

▼ More on Math functions in SQL

Getting the first & Second highest number record from a table

Displaying top 3 records only

PHP Code

SQL Popular Tutorials