Re: more elegant way to write this SQL?

Your query seems to be reasonable. You can still try the query below and compare the execution time. For more details look at analytical functions in oracle8i.

SELECT g.description descr, mt.team_name team_name,

        min(percentile) over (partition by g.description)  FROM games g, member_team mt
 WHERE g.id = mt.game_id

       AND mt.member_id = :b

I am still learning analytical functions, so you may have to make a few modifications to the query if it doesn't run as is. But, I hope you get the idea.

-Madhu S

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<3d6ba71d.1564407579_at_news.globix.com>...
> Basicly in needs description, team_name and min(percentile) from games
> and member_team tables, but since I can't group by description only, I
> am forced to use 2 queries. Is there are a better way to write this?
> (I know I can get rid of the games table until the last step, but it's
> not my concern for now)
>
> Thanx.
>
>
>
> select a.descr AS description, a.team_name, a.percentile
> FROM
> (
> SELECT g.description descr, mt.team_name team_name, percentile
> FROM games g, member_team mt
> WHERE g.id = mt.game_id
> AND mt.member_id = :b
> ) a,
> (
> SELECT g.description descr, MIN(mt.percentile) percentile
> FROM games g, member_team mt
> WHERE g.id = mt.game_id
> AND mt.member_id = :b
> GROUP BY g.description) b
> WHERE a.percentile=b.percentile
> AND a.descr=b.descr
> .......
> We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> remove NSPAM to email
Received on Tue Aug 27 2002 - 19:32:58 CDT