Get the records that has the 3rd maximum value of the given table. The following is a Student's Marks table.

Stu_Id Stu_Name Stu_Marks
1 Naga 98
2 Ram 95
3 Kumar 92
4 Sundar 94
5 Siva 90
6 Bharath 92
7 Ganesh 97
8 Vinod 96
9 Laksh 93
10 Sarath 88
11 Kellis 96

Write a query that prints the students who scored 3rd maximum marks in the given table.

Answer:

select * from tbl_StudentMarks where 

Stu_Marks = 

(select MIN(Stu_Marks) from (

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc) AA)

At first we need to find the 3rd maximum value. So as per this table the thrid maximum value is 96. Two students have scored 96 Marks. So we need to print both rows.

I used two subqueries to obtain the result. In the below statement I am finding the first three top marks.

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc

The above code pritns 98,97,96.
Now I am taking the minimum marks of the above three which is 96 by using the following query.

(select MIN(Stu_Marks) from (

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc) AA

In the above code I used an alias name called "AA" for intermediate data operation purpose.
Now the minimum marks 96 will be checked with outermost query.

select * from tbl_StudentMarks where 

Stu_Marks = 96

At the end of the query we will be geting two rows that is of Vinod and Kellis who scored 96 Marks.