Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Using Analytics......
Hi,
This should work. Concatenate date and text together. Then pick the high and low, and separate the values again.
SQL> create table test (text varchar2(50),month date);
Table created.
SQL> insert into test values ('A',to_date('1-jan-2000','DD-MON-YYYY'));
1 row created.
SQL> insert into test values ('B',to_date('5-may-2000','DD-MON-YYYY'));
1 row created.
SQL> insert into test values
('C',to_date('20-feb-2006','DD-MON-YYYY'));
1 row created.
SQL> select substr(sd,9,1) low
2 , to_date(substr(sd,1,8),'YYYYMMDD') start_date 3 , substr(ed,9,1) high 4 , to_date(substr(ed,1,8),'YYYYMMDD') end_date 5 , result
8 , max(to_char(month,'YYYYMMDD')||text) over () ed 9 , count(*) over () result
L START_DA H END_DATE RESULT
- -------- - -------- ----------
A 01-01-00 C 20-02-06 3
SQL> regards,
Arian
Miggins wrote:
> 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
Received on Fri Feb 24 2006 - 06:54:49 CST