Max or Min Join on BAQ subquery?

wparsonjr · February 20, 2019, 4:42pm

How can I retrieve a single row from a subquery in a BAQ based on a max or min date in that subquery?

Banderson · February 20, 2019, 4:58pm

Subquery options. Order by date first, then return top 1.

or use aggregate functions.

Banderson · February 20, 2019, 5:04pm

and here’s a long thread talking about using those functions.

And make sure you read the tools user guide (it’s right in help) on the BAQ section. They give you step by step examples of using aggregate functions.

ckrusen · February 20, 2019, 5:24pm

I generally make another sub query (call it SubQ2), that just returns the max record (and minimal fields for joining). Then use an inner join between SubQ2 and your original subquery.

Like if you wanted order info (header, lines and releases) for the largest order placed by a customer.

SubQ2 could return OrderHed.Company, OrderHed.OrderNum, OrderHed.CustNum, and MAX(OrderHed.OrderTotal) (the first 3 all being GROUP BY).
The main query would have an inner join between OrderHed and SubQ2. And still have joins to the OrderDtl and OrderRel tables - and not be limited to one record.