Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: table with sparce data
"TurkBear" <john.greco_at_dot.state.mn.us> wrote in message
news:kgrckvk2q124iku6o13b3sskvel71m0g4t_at_4ax.com...
> "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote:
>
> >
> >"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
> >news:Nxt1b.19$3j7.147_at_news.oracle.com...
> >> Min Value Frequency
> >> Name
> >> ---- ------ -----------
> >> a001 0 4
> >> a005 5 1
> >> a006 0 3
> >> a011 3 1
> >
> >Missed one more typo:
> >
> >Min Value Frequency
> >Name
> >---- ------ -----------
> >a001 0 4
> >a005 2 1
> >a006 0 3
> >a011 3 1
> >
> >In other words we keep all rows with value > 0, and compress all the
> >successive rows with value = 0 showing aggregated frequency.
> >
> >
> Still not sure what you want, but try:
>
> select name,value,count(*) from table
> group by name,value;
No, this is not what I meant. Your solution doesn't depend on the order in the input "relation", why my output report definetely depends on it.
Input relation is ordered by name, and each group is defined by the property
prior(value)=value
I can express order explicitly as rownum column added to input relation, and then express my requirements relationally of course (heavily subqueried:-), but I wondered if I'm missing simple analytical solution.
No cursors, please. Received on Fri Aug 22 2003 - 15:15:54 CDT