SELECT class_id, MIN(doc_num) item_lo,MAX(doc_num) item_hi,
status
FROM (
SELECT dt.*, rank() over (Partition By Class_id Order by doc_num) rr
FROM doctest dt
)
GROUP BY class_id,status,rr-doc_num
ORDER BY class_id,MIN(doc_num)
"Bill" <Norwester656_at_hotmail.com> wrote in message
news:5f6e1e81.0303110858.3cb679d7_at_posting.google.com...
> Wondering if any SQL gurus out there have encountered this issue. I
> have a list of items like this in an Oracle 8.17 db:
>
> Item# Class Status
> 1 2 Voided
> 2 2 Voided
> 3 2 Recieved
> 4 2 Recieved
> 5 2 Recieved
> 6 2 Recieved
> 7 3 Recieved
> 8 3 Voided
> 9 4 Recieved
> 10 2 Recieved
>
> I want to return ranges of values grouped by status and Class like
> this:
>
> Class Itemlo ItemHi Status
> 2 1 2 Voided
> 2 3 5 Recieved
> 2 10 10 Recieved
> 3 6 7 Recieved
> 3 8 8 Voided
> 4 9 9 Recieved
>
> I've tried using the first_value and last_value function, but the last
> value jumps to the highest value in that class with that status (10
> for class 2 in this case). Is this possible with SQL... or will I
> need to write some sort of stored procedure with a cursor?
>
> Much thanks to anyone who can figure this one out! (DDL below).
>
> CREATE TABLE DOCTEST (
> DOC_NUM NUMBER,
> CLASS_ID NUMBER (10),
> STATUS VARCHAR2 (12));
>
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 1, 2, 'Voided');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 2, 2, 'Voided');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 3, 2, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 4, 2, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 5, 2, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 6, 3, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 7, 3, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 8, 3, 'Voided');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 9, 4, 'Recieved');
> INSERT INTO DOCTEST ( DOC_NUM, CLASS_ID, STATUS ) VALUES (
> 10, 2, 'Recieved');
> commit;
>
>
> //My failed attempt at returning the values....
>
> Select Class_id,Status,FV,LV
> From
> (Select doc_num,class_id,status,
> First_value(doc_num) Over
> (Partition By Class_id,Status Order by doc_num) as FV,
> Last_value(doc_num) Over
> (Partition By Class_id,Status Order by doc_num ROWS BETWEEN CURRENT
> ROW AND UNBOUNDED FOLLOWING) as LV
> FROM doctest
> ORDER BY doc_num)
> Group By Class_id,FV,LV,Status
Received on Tue Mar 11 2003 - 18:11:13 CST
