Click here to Skip to main content
15,029,944 members
Please Sign up or sign in to vote.
1.11/5 (4 votes)
See more:
i have table- student.which is having fields studentid,studname,mark.i want to get the student name who is having the 3rd highest mark.number of rows are not known,i dont know whether it is required for the calculation,if yes assume any number and give me the answer.thanks in advance
Posted
Updated 22-Feb-21 22:39pm

Hi ,
Check this also
SQL
select MAX(marks) from student where marks <
(select MAX(marks) from student where marks <
(select MAX(marks) from student ))

Best Regards
M.Mitwalli
   
Try this:
SQL
SELECT
    TOP 1 marks
FROM
    (SELECT DISTINCT TOP 3 marks FROM student ORDER BY marks DESC) a ORDER BY marks

Or this one
SQL
WITH Results as
   (SELECT Row_Number() over (ORDER BY marks DESC) as RN,* FROM student)
SELECT * FROM student WHERE RN=3
   
Comments
sravani.v 27-Jun-12 0:08am
   
My 5! for first query
Prasad_Kulkarni 27-Jun-12 0:11am
   
Thank you Sravani!
glad to see you after long time..work load??
sravani.v 27-Jun-12 4:44am
   
No...searching for another job.
Prasad_Kulkarni 4-Jul-12 0:20am
   
Where?
Rahul Rajat Singh 27-Jun-12 3:01am
   
Nice. +5.
Prasad_Kulkarni 27-Jun-12 3:02am
   
Thank you Rahul!
subin joseph 27-Jun-12 6:50am
   
what does 'a' in this query stand for.is it any object
Sebastian T Xavier 28-Jun-12 0:35am
   
google about sql alias....
How about applying the answers to sql query to find 2nd maximum salary[^]?
   
SELECT marks FROM
(
SELECT
marks,
DENSE_RANK()over(order by marks desc)Dn1 FROM student
)as ff where dn1=3;

--this is the best query for finding multiple records exists for same marks.
   
v2
Comments
Richard Deeming 9-Aug-17 11:44am
   
This question was asked, answered, and solved FIVE YEARS AGO.
Santosh kumar Pithani 10-Aug-17 6:00am
   
Let me know that which solution is best for getting particular rank record?
Findout the 3rd highest value:

SELECT mark
FROM employee
GROUP BY mark
ORDER BY mark DESC
LIMIT 2 , 1
   
Comments
CHill60 21-Jun-19 10:01am
   
Reasons for my downvote:
- This doesn't even compile
- Even if it did compile it doesn't answer the question
- The question was answered several times 7 years ago
- Doing anyone's homework for them is not helping them
Try this one



SELECT salary FROM (SELECT DOB FROM Employee ORDER BY DOB DESC) WHERE ROWID = 3
   
v2
Comments
CHill60 23-Feb-21 5:53am
   
Really? No ROWID column and you are ordering by DOB. Nowhere near being a real solution to this 8 year old, already resolved question

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900