Find the 4th or nth highest record in the table

When handling databases and creating tables, we come across a wide variety of queries in order to perform varying operations on the database. In this article, we’ll learn how we can find nth highest record in a table using a single query. This query is of great significance and could even be an interview question for an individual. So let’s get started.

STEP 1 : Create the structure of a table in your database. Name it as ‘student’ and it should contain entries of a student like Id, Name, Marks and Department. 

create table student

(

Id int,

Name varchar(20),

Department varchar(20),

Marks int

)

Writing the above query, we will get a schema of the table containing the above mentioned attributes but we need to add values to the table.

STEP 2 : Insert values to the table according to the attribute.

Insert into student (Id,Name,Department,Marks) values(1,'A','CSE',85) 

Insert into student (Id,Name,Department,Marks) values(2,'B','ECE',55) 

Insert into student (Id,Name,Department,Marks) values(3,'C','ECE',40) 

Insert into student (Id,Name,Department,Marks) values(4,'D','CSE',67) 

Insert into student (Id,Name,Department,Marks) values(5,'E','CSE',90) 

Insert into student (Id,Name,Department,Marks) values(6,'F','CSE',79) 

Insert into student (Id,Name,Department,Marks) values(7,'G','ECE',35) 

Insert into student (Id,Name,Department,Marks) values(8,'H','CSE',87) 

Insert into student (Id,Name,Department,Marks) values(9,'I','ECE',61) 

Insert into student (Id,Name,Department,Marks) values(10,'J','ECE',42) 

The values are successfully inserted into the table using ‘INSERT’ command which is a DML command.

STEP 3 : Using the ‘Select’ command we can view the table inserted with entries.

Select * from student

STEP 4 : Now the question is that we have to find out the 4^th highest marks or we can say Nth highest marks from the above table. Before finding the Nth highest marks we arrange the records in decreasing order based on marks.

select * from student order by marks desc

STEP 5 : We have to get the 4^th highest marks, so what can do is that we can put a sub query within a query such that the sub query gets the top 4 marks arranged in descending order from the table  and then the query arranges the top 4 marks in the ascending order and gets the student with the top 1 marks. This way the 4 highest marks are derived because from the sub query we got the top 4 marks but when these top 4 marks were arranged in ascending order, the lowest i.e. the 4^th highest got placed at 1^st position , so getting the top 1 marks gives us the student with 4^th highest marks.

So first we write the sub query i.e. getting the records of students with top 4 marks.

select top 4 * from student order by marks desc

The output of the sub query will be

In the above sub query we used the following commands

Top :- provides the top records from the table. Here the specified number is 4 so it provides the top 4 records.

Order by :- arranges the records based on an attribute. Here the records are arranged on the basis of marks. By default records are arranged on the basis of ascending order. If we want to arrange them according to descending order, we use DESC.

Now the sub query has to be written within the query.

select top 1 * from (select top 4 * from student order by marks desc) as a order by marks

The output of the query will be

In place of 4, we can write any number say n and we can get the nth highest record from the table.