Group By question

[sblue]

Scores Table:

name / score / gameID

mike....234.....8
dina.....534.....8
john.....463.....6
tom......742.....7
kim......653.....7

How do I get the highest scorer from each game:

dina.....534.....8
john.....463.....6
tom......742.....7

As far as I got:

select max(score), gameID
from scores
group by gameID

Don't know how to conserve the name during the grouping.

Feel like this is basic, but can't figure this out. I tried all kinds of
queries.

[paross1]

I haven't thought this through very well, and I think that you may have
problems with games where you have tie scores, but this this is off the top of
my head.

select distinct s1.game_id, s1.name, s1.score
from scores s1
where s1.score = (select max(s2.score)
from scores s2
where s2.game_id = s1.game_id)

Phil

[sblue]

Ok, that works out, but here is the thing. The SCORES table is really a complex
derived table, and since your solution uses it twice I have two issues:

1) the overall code becomes almost unreadably long if I simply replace SCORES
with the derived table code
2) will performance be affected by deriving the table twice?

I have a feeling you will suggest using views, any other approaches?

[Dan Bracuk]

So Scores is a sub-query? Make it a Cold Fusion query and do a Q of Q, with
the appropriate order by clause, and maxrows = 1. Doesn't solve the problem of
ties though.

Originally posted by: sblue
Ok, that works out, but here is the thing. The SCORES table is really a
complex derived table, and since your solution uses it twice I have two issues:

1) the overall code becomes almost unreadably long if I simply replace SCORES
with the derived table code
2) will performance be affected by deriving the table twice?

I have a feeling you will suggest using views, any other approaches?

[paross1]

Views are OK for certain circumstances, but they usually make things easier for
the programmer, not more efficient for the database engine. Without seeing your
code, it?s hard to comment on how selecting this "table" twice will negatively
affect your performance. Most of that has to do with how well your code is
optimized, your use of indexes, etc. You should avoid doing things like using
function calls on fields within the WHERE clause (like data conversion
functions, etc.) because that usually negates the use of indexes on those
fields, etc. I might be able to offer suggestions if you were to include your
derived table, or your original query, unless it is prohibitively large.

Good luck,
Phil

[sblue]

Paross,

Using your example, I was able to come up with something that worked out well. Thanks for all the advice!