Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Range of values by item
On Jul 2, 6:06 pm, Tim Frawley <tim.fraw..._at_alaska.gov> wrote:
> Hello all, I am hoping someone likes a challenge and is willing to
> help me out.
>
> I have data that looks like so:
>
> create table READING (specimen_id varchar2(3), reading_number
> varchar2(1), reader varchar2(3));
>
> insert into READING values ('1','1','MWLOVEJOY');
<SNIP>
> insert into READING values ('330','1','JWCASHEN');
>
> This is the SQL I am using which works but doesn't display the second
> group of read results for MWLOVEJOY, it lumps those reads in with
> KFVAN_KIRK. See the results at the very bottom.
>
> select distinct START_
> ,END_
> ,READER
> from (
> select
> first_value(SPECIMEN_ID) over (partition by DIFF)
> START_
> ,last_value(SPECIMEN_ID) over (partition by DIFF) END_
> ,READER
> ,row_number() over (partition by DIFF order by
> SPECIMEN_ID) R
> from (
> select
> row_number() over(partition by READER order by
> SPECIMEN_ID) - SPECIMEN_ID DIFF
> ,READER
> ,SPECIMEN_ID
> from
> READING R1
> )
> )
> where
> R=1
> order by
> START_
>
> Results look like:
>
> 1 001 100 MWLOVEJOY
> 2 101 300 KFVAN_KIRK
> 3 301 330 JWCASHEN
>
> I need results that look like:
>
> 1 001 100 MWLOVEJOY
> 2 101 200 KFVAN_KIRK
> 3 201 300 MWLOVEJOY
> 4 301 330 JWCASHEN
Excellent tips provided by Rob van Wijk.
I initially thought that the solution that I created was incorrect, as the output did not match your output - but it does match the output by generated by Rob van Wijk's SQL statement. I have included a slightly different solution below:
The starting point looks like this:
SELECT
READER,
TO_NUMBER(SPECIMEN_ID) SPECIMEN_ID,
DECODE(TO_NUMBER(SPECIMEN_ID)-TO_NUMBER(LAG(SPECIMEN_ID) OVER
(PARTITION BY READER ORDER BY TO_NUMBER(SPECIMEN_ID))),1,0,1) D
FROM
READING;
...
READER SPECIMEN_ID D ------------------------------ ----------- ---------- MWLOVEJOY 99 0 MWLOVEJOY 100 0 MWLOVEJOY 201 1 MWLOVEJOY 202 0 MWLOVEJOY 203 0 MWLOVEJOY 204 0...
What this does is to set a flag, D in this case, when there is a gap of more than one in the numbers for a READER, as shown above.
We can then slide the above SQL statement into an inline view and sum
the flag column (D) so that all of those that should be in the same
sequence may be kept together:
SELECT
READER,
SPECIMEN_ID,
SUM(D) OVER (PARTITION BY READER ORDER BY SPECIMEN_ID) C
FROM (
SELECT
READER,
TO_NUMBER(SPECIMEN_ID) SPECIMEN_ID,
DECODE(TO_NUMBER(SPECIMEN_ID)-TO_NUMBER(LAG(SPECIMEN_ID) OVER
(PARTITION BY READER ORDER BY TO_NUMBER(SPECIMEN_ID))),1,0,1) D
FROM
READING);
...
READER SPECIMEN_ID C ------------------------------ ----------- ---------- MWLOVEJOY 99 1 MWLOVEJOY 100 1 MWLOVEJOY 201 2 MWLOVEJOY 202 2 MWLOVEJOY 203 2 MWLOVEJOY 204 2...
The final step is to find the min and max for each of the groups,
using the sum of the flag column as a secondary grouping column:
SELECT
TO_CHAR(MIN(SPECIMEN_ID),'000') START_VAL,
TO_CHAR(MAX(SPECIMEN_ID),'000') END_VAL,
READER
FROM (
SELECT
READER,
SPECIMEN_ID,
SUM(D) OVER (PARTITION BY READER ORDER BY SPECIMEN_ID) C
FROM (
SELECT
READER, TO_NUMBER(SPECIMEN_ID) SPECIMEN_ID, DECODE(TO_NUMBER(SPECIMEN_ID)-TO_NUMBER(LAG(SPECIMEN_ID) OVER(PARTITION BY READER ORDER BY TO_NUMBER(SPECIMEN_ID))),1,0,1) D FROM
STAR END_ READER
---- ---- ----------
001 200 KFVAN_KIRK
001 100 MWLOVEJOY
101 190 JWCASHEN
201 300 MWLOVEJOY
301 330 JWCASHEN
A bit less clean:
SELECT DISTINCT
TO_CHAR(MIN(SPECIMEN_ID) OVER (PARTITION BY READER,C),'000')
START_VAL,
TO_CHAR(MAX(SPECIMEN_ID) OVER (PARTITION BY READER,C),'000')
END_VAL,
READER
FROM (
SELECT
READER,
SPECIMEN_ID,
SUM(D) OVER (PARTITION BY READER ORDER BY SPECIMEN_ID) C
FROM (
SELECT
READER, TO_NUMBER(SPECIMEN_ID) SPECIMEN_ID, DECODE(TO_NUMBER(SPECIMEN_ID)-TO_NUMBER(LAG(SPECIMEN_ID) OVER(PARTITION BY READER ORDER BY TO_NUMBER(SPECIMEN_ID))),1,0,1) D FROM
If nothing else, this shows the flexibility of Oracle's analytical functions.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue Jul 03 2007 - 06:18:58 CDT