Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using Analytics......
"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.htmlReceived on Fri Mar 03 2006 - 09:58:44 CST