Re: Is this possible

Bill wrote:
> 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

assuming your INSERT below is correct this one is 6 3 Received Right?

> 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 think you are making it more complicated than it needs to be. have you tried something like

Select class_id, min(doc_num) Itemlo, max(doc_num) itemhi, status from doctest
group by class_id, status
order by class_id;

Note: this is untested.

> 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).
[]

General rule: try the simple approach first.

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost