RE: Some OLAP function perhaps?

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

A couple of problems with my prior post, changed below...

> At/after V5R4 you can use the RANK function to pull only the top 10 by
> library.  You'd union your result with another query that included
> SUM().  I'm not at V5R4 on the systems at my disposal....  Something
> like:
>
> WITH totrows as
>    (select odlbnm, sum(odobsz) as libsize
>     from mytable
>     where odobtp<>'*LIB'
>     group by odlbnm)
> ,  topten as (select odobnm, odobtp, odobtp, rank(see manual) as
> ranknbr
>               from mytable
>               ...)
> Select odlbnm, odobnm, odobtp, odobsz
> From topten

Where ranknbr < 10

> Union
>    Select odlbnm, '', '', libsize
>    From totrows
>    )
> Order by odlbnm, odobsz desc
>
> Dennis Lovelady
> http://www.linkedin.com/in/dennislovelady


> > I want to generate results like the following from a table with those
> > columns, however, the summary row does not exist.  And, of the detail,
> > I
> > only want the 10 largest rows per library.
> >
> > Library  Object  Size
> > BIGLIB           456789
> > BIGLIB   BIGOBJ  200000
> > BIGLIB   MEDOBJ  100000
> > BIGLIB   SMLOBJ   50000
> > ...
> > MEDLIB           322457
> > MEDLIB   BIGOBJ  150492
> > MEDLIB   MEDOBJ   54329
> > MEDLIB   SMLOBJ   12222
> > ...
> > SMLLIB           105298
> > SMLLIB   BIGOBJ  102201
> > SMLLIB   MEDOBJ      49
> > SMLLIB   SMLOBJ       2
> > ...