Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Range of values by item

Re: Range of values by item

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 03 Jul 2007 04:18:58 -0700
Message-ID: <1183461538.185751.271180@k79g2000hse.googlegroups.com>


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
      READING))
GROUP BY
  READER,
  C
ORDER BY
  1,
  3;

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
      READING))
ORDER BY
  1,
  3;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US