Re: more elegant way to write this SQL?

From: Marc Blum <marc_at_marcblum.de>
Date: Wed, 28 Aug 2002 21:17:42 +0200
Message-ID: <u68qmucpn1u9k1qvvj2nn82s0v1jh0g186@4ax.com>

On Tue, 27 Aug 2002 17:28:25 GMT, andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>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

SELECT descr, team_name, percentile
  FROM (
SELECT g.description AS descr,

      mt.team_name AS team_name,
      percentile,  
     RANK() OVER (PARTITION BY g.description ORDER BY percentile DESC) AS rk
  FROM games g, 
       member_team mt

 WHERE g.id = mt.game_id

    AND mt.member_id = :b
) v
 WHERE rk = 1
/

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Wed Aug 28 2002 - 14:17:42 CDT