How to Select Min(Field) in a BAQ?

I’ve got a query that returns various information about open jobs. For each job I want to display a couple fields from the JobOper table for the MIN(OperSeq) Where OpComplete = 0. I have the Where clause working via the criteria on the JobOper table, but can’t figure out a simple way to select only 1 JobOper row per job - the row with the MIN(OperSeq).

I can get this to work via a subquery that displays the min OperSeq (and company/jobnum for joining purposes), then on the top level I join to a second copy of the JobOper table to the subquery so I can display other fields in the row(OpDesc, StartDate, etc) but it seems terribly inefficient.

Any pointers for a BAQ newbie?

1 Like

Sort and group by should do it.

It seems like overkill but it works, and I can’t find a more efficient way to achieve this. I added a subquery(Next_OperSeq) that displays the next uncompleted OperSeq(via min() calculated field). I then join that to a second copy of the OperSeq so I can show other fields(e.g. description) for the row (of the next uncompleted operation.

image

1 Like