SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query Help Required

    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?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    who scored 3rd?

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    who scored 3rd?
    List of student IDS who are ranked at 3rd place based on marks obtained column.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Arvind_Reloaded View Post
    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?
    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!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Use a subquery that gets the 3rd highest score, and join that to the table to get those with that score.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Use a subquery that gets the 3rd highest 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    you're suggesting LIMIT in the subquery, right?

    too bad that doesn't always work
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Why not?
    because sometimes it just doesn't, that's why not

    http://dev.mysql.com/doc/refman/5.0/...ry-errors.html

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    because sometimes it just doesn't, that's why not

    http://dev.mysql.com/doc/refman/5.0/...ry-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).

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    So if you have

    Erik 90
    Peter 90
    Maria 90
    John 85
    Alice 85
    Suzan 80
    Rick 80

    Who do you want to extract?

  14. #14
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •