Re: SQL statement

From: Klaus Zeuch <KZeuch_at_nospamhotmail.com>
Date: Thu, 17 Aug 2000 00:15:48 +0200
Message-ID: <8nf3pn$l4v$18$1@news.t-online.com>

Assuming the best case (Oracle 8.1.6):

select player, sum(actual_points) points from (

      select player, actual_points,
             row_number() over (partition by player order by actual_points
desc) rank
      from   entries_table
     )
where rank < 5 and player in (
      select player
      from   entries_table
      group  by  player
      having count(*) > 3)

group by player
having sum(actual_points) between 0 and 50000 order by points desc;

Klaus

"Joseph Ranseth" <jransethNO_SPAM_at_worldcupfishing.com> schrieb im Newsbeitrag news:mjCm5.1355$ak3.12121_at_news1.mts.net...
> Hello all,
> I need to build a fairly complex SQL statement, and I need some help.
> I want to pull all the entries for each user from a table, where a user
 has
> at least 4
> records associated to them, and the sum of a field of the top 4 entries
 are
> between 2 parameters. I want to sort the records on the sum of the top 4
> records in descending order.
> Something like this (although I know this doesn't work)
>
> SELECT sum(actual_points),
> player_name
> FROM (SELECT ROWNUM as Rank,
> player_name,
> actual_points
> FROM (SELECT player_name,
> actual_points
> FROM entries_table
> ORDER BY actual_points DESC
> )
> WHERE rank > 5
> )
> WHERE sum(actual_points) > 2000
> AND sum(actual_points) < 5000
> /
>
> I can't figure out how to make this work....
> Thanks in advance for any help!
> --
> Joseph
>
>
>
>
Received on Wed Aug 16 2000 - 17:15:48 CDT