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.

 Posted by Nagasundar_Tn on 11/17/2012 | Category: Sql Server Interview questions | Views: 3254 | Points: 40

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.

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Lexminathan on: 11/20/2012 | Points: 10
Answer for Oracle .. .

select * from Test_Marks where studmarks =
(select min(studmarks) from (
select studid,studmarks,studname,ROW_NUMBER() OVER (PARTITION BY studmarks ORDER BY studmarks DESC) rank
from Test_Marks order by studmarks desc) where rownum between 1 and 3) ;
Posted by: Yogi.Josh on: 11/22/2012 | Points: 10
I think it should be:
1. Get distinct marks of students. This will avoid duplications and triplications
2. Get min of top 3 from above distinct marks
3. Now you have got a figure which is 3rd from top, run select query --- done.
select * from StudentMarks where marks=(
select MIN(marks) from (
select top 3 marks from (
select distinct marks from StudentMarks
) as Top3Marks
order by marks desc
) as Top3rdMark
Posted by: aswinialuri-19361 on: 11/22/2012 | Points: 10
we will use like this also

for 3rd highest salary
select top 3 salary
from (select distinct top 3 salary from employee order By salary desc )a order by salary.

Login to post response