Min Req. Date in BAQ

[JasonMcD (Jason McDermott)]

@Rick23S

There are some other fields in OrderRel I want to add, but I’d like the keep the rows limited to rows with the earliest Req Date.

I think this has been covered before, but I can’t seem to find the post.

You are asking, “Give me the earliest ReqDate for this part AND tell me what order/line it is for and the customer, etc.” Right?

Far as I know, the only way would be to add the OrderDtl table to the query again and join to it. Well, and OrderRel and whatever else and do another subquery. Here:

Join from Part to SQ1:

Join from SQ1 to SQ2:

SQ1 and grouping:

SQ2:

Results:

(PS, sort by part number…)

Of course, what if two orders are due the same day? Then you get duplicates. But, how else could you avoid that? Which order is the “right” one?

Also, this one comes up a lot:

Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: [image] [image] SubQuery2: [image] [image] Result: [image] [image] Basically in a simpler view it does SELECT (SELECT count(*) FROM table WHERe...) as YourCalculate…