#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    12
    Rep Power
    0

    How to Extract only Max Scores


    I need to Extract only the Record that have the Max scores. Example

    Table A
    Name Subject Scores
    ---------------------------------
    A S1 10
    A S2 30
    A S3 20
    B S4 20
    B S5 30
    C S6 50
    C S7 10
    ---------------------------------

    Result Table
    Name Subject Scores
    ---------------------------------
    A S2 30
    B S5 30
    C S6 50
    ---------------------------------

    How can I do this using SQL command? Please suggest.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    google "rows holding groupwise maximum" -- many tutorials exist on this exact problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    10
    select max(scores) as maxScoreX
    from TableA
    group by Name
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by emailx45
    select max(scores) as maxScoreX
    from TableA
    group by Name
    that will produce the following results --

    maxScoreX
    -----------
    50
    30
    30

    which is not really what was requested, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    12
    Rep Power
    0
    Originally Posted by emailx45
    select max(scores) as maxScoreX
    from TableA
    group by Name
    Name and Subject are required in the result row as
    Result Table
    Name Subject Scores
    ---------------------------------
    A S2 30
    B S5 30
    C S6 50
    ---------------------------------
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    12
    Rep Power
    0
    if I use

    select Name, Max(Scores) from TableA group by Name

    I have Name and Max Scores of Each name but No Subject.

    Name maxScoreX
    -------------------------
    C 50
    B 30
    A 30

    That is not also what I want because it lacks of Subject.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    did you read any of the articles i suggested?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    12
    Rep Power
    0
    Originally Posted by r937
    did you read any of the articles i suggested?
    Thank you so much. I've just read and got the solution like this.

    SELECT NAME, SUB, SCORE
    FROM TEST s1
    WHERE SCORE=(SELECT MAX(s2.SCORE)
    FROM TEST s2
    WHERE s1.NAME = s2.NAME);
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    74
    Rep Power
    10
    TRY IT

    select NAME, (SUB||"") as xSUB, max(scores) as maxScoreX
    from TableA
    group by Name
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by emailx45
    TRY IT

    select NAME, (SUB||"") as xSUB, max(scores) as maxScoreX
    from TableA
    group by Name
    no, sorry

    please read this entire paragraph, especially the bold parts --
    MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
    -- MySQL :: MySQL 5.6 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY
    indeterminate means that the subject value chose for each name is ~not necessarily~ the correct subject value that goes along with the max score

    sorry, but that's the way it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    242
    Rep Power
    17
    The following statement will work if name/subject combination is unique.
    Code:
    with n as (
    select
        a.name,
        max(score) as scores
    from
        a 
    group by 1)
    
    select
        a.subject,
        n.name,
        n.scores
    from
        a inner join na on n.name = a.name and a.scores = n.scores

IMN logo majestic logo threadwatch logo seochat tools logo