Re: SUM totals by relative appearance in output

            FROM (SELECT dt, st, MAX (rn) OVER (ORDER BY dt) max_rn
                    FROM (SELECT dt,
                                 st,
                                 CASE
                                    WHEN LAG (st, 1) OVER (ORDER BY dt) = st
THEN NULL
                                    ELSE ROW_NUMBER () OVER (ORDER BY dt)
                                 END
                                    rn
                            FROM k2))
        GROUP BY st, max_rn
        ORDER BY 1;

where st is filename, dt is starting,

Mike

On Thu, Jun 30, 2011 at 3:11 PM, Herring Dave - dherri < Dave.Herring_at_acxiom.com> wrote:

> Folks,
>
> I'm having a bit of a mental block dealing with a query I'm trying to
> write. What I'd like to do is for data in a table, ordered by some value
> when queried, to only display totals when rows are repeating by a value, but
> only doing this totalling by their relative position in the output. (I'm
> sure there's a better way to say that, but right now even that's beyond my
> scope.)
>
> Let's say I was trying to map out relative position of files in ASM by
> their AU position. FILE1 could have 1-to-many consecutive AUs, then another
> file or free space, then FILE1 could repeat. For example:
>
> FILE NAME AU POS
> --------- ------
> FILE1 1
> FILE1 2
> FILE2 3
> FILE3 4
> FILE3 5
> FILE3 6
> FILE1 7
> ...
>
> For the above data I'd want to see:
>
> FILE NAME STARTING AU POS LENGTH
> --------- --------------- ------
> FILE1 1 2
> FILE2 3 1
> FILE3 4 3
> FILE1 7 1
> ...
>
> I'm convinced I can use an analytic function with the right windowing
> option to do this all in SQL, but so far I've failed miserably. Obviously I
> could write PL/SQL to do this, but I've overflown DBMS_OUTPUT's capacity and
> ended up moving on to capturing output and manipulating it with perl. But
> that seems like a waste because again, I'm convinced I can do this in SQL
> without forcing a fork onto something else.
>
> Does any of this make sense and does anyone have a way to do this with SQL?
>
> DAVID HERRING
> DBA
> Acxiom Corporation
> EML dave.herring_at_acxiom.com
> TEL 630.944.4762
> MBL 630.430.5988
> 1501 Opus Pl, Downers Grove, IL 60515, USA
> WWW.ACXIOM.COM
>
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be legally
> privileged. If the reader of this message is not the intended recipient, you
> are hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please resend this communication to the sender and
> delete the original message or any copy of it from your computer system.
> Thank you.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l