I have a table with columns StudentID and MarksObtained
How can i get the list of students who scored 3 rd from marks obtained column?
What is the query I should Write?
who scored 3rd?
List of student IDS who are ranked at 3rd place based on marks obtained column.
this is ~so~ obviously a homework assignment
we won’t write your query for you
we can, however, give you a hint
go to the mysql manual and look up LIMIT
be aware, however, that you must also handle situations like this –
NAME MARK
Todd 92
Biff 91
Fred 90
Jack 90
Matt 88
note that both Fred and Jack are 3rd!!!
This is a interview question. And Exactly I need fred and jack as output. Limit is how many no of rows to return.KIndly help me with the query
Use a subquery that gets the 3rd highest score, and join that to the table to get those with that score.
you’re suggesting LIMIT in the subquery, right?
too bad that doesn’t always work 
there is, however, another approach…
pseudo-code –
SELECT name,mark FROM daTable
WHERE (the number of people with a higher mark than the current row) < 3
for todd, the number of people with a higher mark is 0
for biff, the number of people with a higher mark is 1
for fred, the number of people with a higher mark is 2
for jack, the number of people with a higher mark is 2
for matt, the number of people with a higher mark is 4
hence “less than 3” in the WHERE clause
Why not?
there is, however, another approach…
pseudo-code –
SELECT name,mark FROM daTable
WHERE (the number of people with a higher mark than the current row) < 3
for todd, the number of people with a higher mark is 0
for biff, the number of people with a higher mark is 1
for fred, the number of people with a higher mark is 2
for jack, the number of people with a higher mark is 2
for matt, the number of people with a higher mark is 4
hence “less than 3” in the WHERE clause
nice 
because sometimes it just doesn’t, that’s why not
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
Thanks for the link. Maybe I used the wrong term (subquery). I was think about joining the table to a subset of itself (and that subset would contain only the 3rd highest vote. This solution probably also might not work in some older MySQL versions?
And of course it presumes that 3rd highest vote means not only 2 people with higher votes, but only two higher votes exist (and each of these higher votes might be assigned to more people).
3rd highest mark is not the same as person in 3rd place
it’s not at all clear from the original poster which result is desired
We need list of students with 3rd highest marks
So if you have
Erik 90
Peter 90
Maria 90
John 85
Alice 85
Suzan 80
Rick 80
Who do you want to extract?
This is what i want
Suzan 80
Rick 80
You can choose between a slightly modified version of Rudy’s solution:
pseudo-code –
SELECT name,mark FROM daTable
WHERE (the number of marks higher than the current row) < 3
or mine:
pseudo-code –
SELECT name,mark FROM daTable
INNER JOIN (select the third highest mark from daTable using order by and limit)
WHERE mark = third highest mark