Min Function 2

[Abslag]

I am working on a report that is tricky for me. I am sure there are many of you out there that could help. I am trying to identify the Minimum number of a given part number and not the min of the whole column

Parent_Part Jr_Part_Number Qty Min
20-000 20-111S 5 2
20-000 13-555G 2 2
20-000 12-321J 15 2
20-555 12-3456 12 6
20-555 05-321G 10 6
20-555 16-1632 6 6


The Min column is my desired result. It is min qty of the Jr_Part Number and grouped by the parent part number.

Can anyone help

 

[SantaMufasa]

wbslag,

Here is code you can use:

select a.*,minval
from part a,(select parent_part,min(qty)minval from part group by parent_part)b
where a.parent_part = b.parent_part;

PARENT JR_PART        QTY     MINVAL
------ ------- ---------- ----------
20-000 20-111S          5          2
20-000 13-555G          2          2
20-000 12-321J         15          2
20-555 12-3456         12          6
20-555 05-321G         10          6
20-555 16-1632          6          6

Let us know if this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Abslag]

Thank you King Mufasa,
I will give it a try, it looks easy enough after it is spelled out.
Appreciate the help and I will let you know

 

[Abslag]

Santa,
I think your name is Santa because you are so giving. Thanks for the help, it worked and I forgot to say anything. Thanks Brother

 

[djbjr]

this would work as well and I wonder if it would be faster....


select part, Jr_Part_Number, Qty,
MIN(qty) OVER (PARTITION BY parent) min_qty
from parts


Let me know how this works for ya

 

[Abslag]

djbjr,
Thanks for the input, yours even looks simpler. I have already finished the report and the performance is quite good but gratefully the code is there for any other users that might happen upon this thread.
********************