Retrieving the first record from Job Prod when there is more than one demand link

I am working on a BAQ where I need to return the first OrderNum, OrderLine, OrderRelNum from the JobProd table when there are more than one Order Demand.

One of the challenges I come across is that the jobprod might have the same sales order number, the same orderline repeated multiple times because there is more than one release. I also have data where it is the same sales order number, different sales order lines and releases could be 1, 2 or any other number.

I have tried several method but still no love…any ideas?

Check out windowing functions. You can rank them based on a set of criteria. (So order and line number , ordered by release number), then filter that subquery to only show the 1s.

1 Like

Well, after some additional work (and beating my head against hard surfaces), I was able to resolve my own issue.
I just needed to create a BAQ with the JobProd Table selecting the JobNumber and then a calculated field for the Min OrderNum, another Calculated field for the Min OrderLine and another Calculated field for Min OrderRelNum. Then select the Group by option on the JobNum field…