chiprivers Posted April 18, 2007 Share Posted April 18, 2007 Lets say that you have a table with two columns: x and y You pull a query: SELECT x, y FROM table GROUP BY x This will only return one record for each value of x, nut my question is which record does it use for the value of y? And can you select which record is used to return y using an ordering rule? Quote Link to comment Share on other sites More sharing options...
veridicus Posted April 18, 2007 Share Posted April 18, 2007 This SQL works in mysql, but it's bad. If you want distinct records, use DISTINCT. Use group by on all columns which aren't in an aggregate function. Quote Link to comment Share on other sites More sharing options...
chiprivers Posted April 18, 2007 Author Share Posted April 18, 2007 This SQL works in mysql, but it's bad. If you want distinct records, use DISTINCT. Use group by on all columns which aren't in an aggregate function. Sorry but you have lost me, let me explain exactly what I want to do: in my previous example, column x will hold a name, column y will hold a score: col x col y Bob 100 John 150 Dan 200 Bob 150 Arthur 175 John 200 What I want to be able to do is return a name along with the highest score for that person, so I will use GROUP BY on col x to return only one row for each person but how do I select the highest value in col y for each person to be returned along with their name? Quote Link to comment Share on other sites More sharing options...
veridicus Posted April 18, 2007 Share Posted April 18, 2007 Oh... SELECT x, max(y) FROM table GROUP BY x In this case "max" is the aggregate function. You'll get just the highest value for y for each value of x. Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted April 19, 2007 Share Posted April 19, 2007 Lets say that you have a table with two columns: x and y You pull a query: SELECT x, y FROM table GROUP BY x This will only return one record for each value of x, nut my question is which record does it use for the value of y? And can you select which record is used to return y using an ordering rule? The first record for each unique `x` group is returned if no group by function is applied to `y`, but apparently there is no guarantee of this happening. Quote Link to comment Share on other sites More sharing options...
btherl Posted April 19, 2007 Share Posted April 19, 2007 The first record for each unique `x` group is returned if no group by function is applied to `y`, but apparently there is no guarantee of this happening. The problem being that "first" is not defined here, and may change if the query plan changes, or if the ordering of data as physically stored on the disk changes. It's suitable for cases where you want any value of y and you don't care which one it is. This often happens when you are joining tables. Quote Link to comment Share on other sites More sharing options...
chiprivers Posted April 20, 2007 Author Share Posted April 20, 2007 So what I could do, for the given example, is : SELECT x, y FROM table GROUP BY x ORDER BY x ASC, y DESC Would that give me one record for each unique value of x using the record that has the highest value for y?? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 20, 2007 Share Posted April 20, 2007 No... if you wanted to do that, you'd use: SELECT x, MAX(y) FROM table GROUP BY x ORDER BY x ASC Quote Link to comment Share on other sites More sharing options...
chiprivers Posted April 20, 2007 Author Share Posted April 20, 2007 No... if you wanted to do that, you'd use: SELECT x, MAX(y) FROM table GROUP BY x ORDER BY x ASC I thought from the prvious posts that the first occurence of any records with the same value for x would be returned with the rest of the values in that same record, this by ordering the y column descening, that would mean the record with the highest value of y would be returned. Is this not right?? I know that for that exact example, the max(y) option may be simpler but in theory you you could use any rule to order the y column and control which value is returned. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 20, 2007 Share Posted April 20, 2007 That's not true... group by will return the "first" record it finds, and doesn't even know about the ORDER BY clause. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.