Max or Min Join on BAQ subquery?

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

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



or use aggregate functions.

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.

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.
1 Like