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

    Join Date
    Mar 2004
    Posts
    27
    Rep Power
    0

    Simple theory question


    This is more a theory question than a specific advice, but here it goes:

    Let's say I want to select the name of the tallest player on each basketball team. the data has the following rows:

    player_data::
    player_name, player_team, player_height


    if I do:

    SELECT player_team, max(height), player_name
    FROM player_data
    GROUP BY player_team

    Now - there is no guarantee that player_name will be from the same row as max(height) correct? How do I write this type of query?

    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by manzellb
    Now - there is no guarantee that player_name will be from the same row as max(height) correct?
    that is correct

    in fact, ms sql server will not even run that query (mysql would), because it has a syntax error -- there's a column in the SELECT clause that isn't in the GROUP BY clause

    Originally Posted by manzellb
    How do I write this type of query?
    develop it in two steps

    first, take the player name out of the SELECT clause

    this gives you the max height per team

    after you're comfortable that it's returning the right results, put it into a subquery, and join that subquery back to the table, joining on team and maxheight
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo