Re: Using Analytics......

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 24 Feb 2006 14:10:34 +0100
Message-ID: <43ff0699$0$493$9b4e6d93@newsread4.arcor-online.net>

Miggins schrieb:
> 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
>

In the result set rows are unordered, if you don't specify an order by, so in general you can not speak about first/last record. Assumed , you ordering column is the date, then it could look like:

scott_at_ORA102> create table t(id varchar2(1),dt date);

Table created.

scott_at_ORA102> insert into t values('A',DATE '2000-01-01');

1 row created.

scott_at_ORA102> insert into t values('B',DATE '2000-05-05');

1 row created.

scott_at_ORA102> insert into t values('C',DATE '2006-02-20');

1 row created.

scott_at_ORA102> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

scott_at_ORA102> alter session set nls_date_language='AMERICAN';

Session altered.

scott_at_ORA102> SELECT f_Id, f_Dt, l_Id, l_Dt, Cnt

   2 FROM (SELECT First_Value(Id) Over(ORDER BY Dt Rows BETWEEN Unbounded Preceding AND Unbounded Following) f_Id,

   3                 First_Value(Dt) Over(ORDER BY Dt Rows BETWEEN 
Unbounded Preceding AND Unbounded Following) f_Dt,
   4                 Last_Value(Id) Over(ORDER BY Dt Rows BETWEEN 
Unbounded Preceding AND Unbounded Following) l_Id,
   5                 Last_Value(Dt) Over(ORDER BY Dt Rows BETWEEN 
Unbounded Preceding AND Unbounded Following) l_Dt,
   6                 COUNT(*) Over() Cnt
   7          FROM   t)

   8 where rownum=1;

F F_DT                 L L_DT                        CNT
- -------------------- - -------------------- ----------
A 01-JAN-2000          C 20-FEB-2006                   3

1 row selected.

Best regards

Maxim Received on Fri Feb 24 2006 - 07:10:34 CST