Re: SQL conditional aggregation...

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:<bi4qc9$4tq7t$1_at_ID-114658.news.uni-berlin.de>...
> 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.
>

1. I don't see any need for grouping here.
2. You need to use subquery, like:

select entity, name, score as best_score from ....
where score = (select max(score) from ....)

> Any help greatly appreciated.
> Best Regards,
> Giovanni
Received on Fri Aug 22 2003 - 10:00:07 CDT