Klaus....Thank you, although this raises a problem that I recall you dealt
with earlier, it will work fine in sql*plus, but won't compile in a stored
procedure.
I can put it in a view, but is there any way I can pass it parameters?
Otherwise I will be forced to created quite a large number of views to
handle this.
Thanks for your help!!!
JR
--
"Klaus Zeuch" <KZeuch_at_nospamhotmail.com> wrote in message
news:8nf3pn$l4v$18$1_at_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:50:01 CDT
