SQL conditional aggregation...

Hello all,

I have a query that returns a set of: names union alternates (aka), entity id and a score like this:

ENTITY_ID   NAME                    SCORE
1           Giovanni                23
1           bravegag                45
1           Giosvi                  56
1           El Puli en la Habana    98
2           Hussein                 5
3           Bin Ladin               3

Note the first 4 rows belong to the same person, in case entity_id=1 the first
is the real name and remaining rows the
alternates.

Now I need to show the "Best Match" the name for which the SCORE is the highest grouping by ENTITY_ID e.g.

"El puli en la Habana" with 98

but neither of the folloing is accepted as valid:

SELECT CASE WHEN score=max(score) THEN name END as best_score FROM ...
GROUP BY ENTITY_ID SELECT DECODE(score, max(score), name) as best_score FROM ...
GROUP BY ENTITY_ID nor wrapping those above with MAX, MIN.

Any help greatly appreciated.
Best Regards,
Giovanni Received on Fri Aug 22 2003 - 05:11:54 CDT