sql query for getting the 3rd highest value

Question

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 26-Jun-12 17:00pm

subin joseph

Updated 22-Feb-21 22:39pm

Add a Solution

6 solutions

Solution 3

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

Posted 26-Jun-12 20:40pm

Mohamed Mitwalli

Solution 1

How about applying the answers to sql query to find 2nd maximum salary[^]?

Posted 26-Jun-12 17:21pm

barneyman

Solution 5

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.

Posted 8-Aug-17 20:30pm

Santosh kumar Pithani

Updated 8-Aug-17 20:33pm

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?

Solution 6

Findout the 3rd highest value:

SELECT mark
FROM employee
GROUP BY mark
ORDER BY mark DESC
LIMIT 2 , 1

Posted 21-Jun-19 2:43am

Member 14508064

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

Solution 7

Try this one

SELECT salary FROM (SELECT DOB FROM Employee ORDER BY DOB DESC) WHERE ROWID = 3

Posted 22-Feb-21 22:39pm

Member 14493602

Updated 22-Feb-21 22:43pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Prasad_Kulkarni · Accepted Answer · 2012-06-26T17:54:00

Solution 2

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

Posted 26-Jun-12 17:54pm

Prasad_Kulkarni

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