Re: Using Analytics......

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Mar 2006 15:58:44 +0000 (UTC)
Message-ID: <du9p3k$2np$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Miggins" <mtproc_at_yahoo.co.uk> wrote in message news:1140780201.046227.240990_at_e56g2000cwe.googlegroups.com...
> Hi,
>
> Wanting to run a query which will do the following.
>
> Extract information from the first and last record in the query and
> also the number of records processed.
>
> So a table containing the following.....
>
> A 01-JAN-2000
> B 05-MAY-2003
> C 20-FEB-2006
>
> Would return.....
>
> A 01-JAN-2000 C 20-FEB-2006 3
>
> I know you can do this using analytics.....any ideas....also any
> resources explaining more about them.
>
> Cheers
>

Depends where and how you do your ordering, but here's an example of the type of thing that you could do. It partitions the data before 'ordering', but doesn't actually order it since you only want the highest and lowest.

create table t1 (
 n1 number,
 d1 date
);

insert into t1 values (1, trunc(sysdate) - 2);
insert into t1 values (1, trunc(sysdate) + 2);
insert into t1 values (1, trunc(sysdate) - 1);
insert into t1 values (1, trunc(sysdate) + 1);

insert into t1 values (2, trunc(sysdate) - 2);
insert into t1 values (2, trunc(sysdate) + 2);

insert into t1 values (2, trunc(sysdate) - 5); insert into t1 values (2, trunc(sysdate) + 1);

commit;

select distinct *
from
(
select
 n1,
 min(d1) over(partition by n1) fv,
 max(d1) over(partition by n1) lv,
 count(*) over(partition by n1) ct
from
 t1
)
;

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Fri Mar 03 2006 - 09:58:44 CST