grouping records

[chiprivers]

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?

[veridicus]

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.

[chiprivers]

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?

[veridicus]

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.

[bubblegum.anarchy]

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.

[btherl]

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.

[chiprivers]

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??

[fenway]

No... if you wanted to do that, you'd use:

 

SELECT x, MAX(y) FROM table GROUP BY x ORDER BY x ASC

[chiprivers]

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.

[fenway]

That's not true... group by will return the "first" record it finds, and doesn't even know about the ORDER BY clause.