Selecting MAX in query

[Mike Hughes]

This query returns the results shown below it.

I would like to pick up the MAX "GLOBAL_BEG" and in this case it would return only one line:
CASE...........ID.........TYPE...MED........GLOBAL_BEG
20139847P.CARDCFM....C...... A.......... 431580408

Can this query be changed to do this and if yes would you show me how.

Thanks Mike

SELECT
NOLDBA_SUPPORT_ORDER.CASE,
NOLDBA_SUPPORT_ORDER.ID,
NOLDBA_SUPPORT_ORDER.TYPE,
NOLDBA_SUPPORT_ORDER.MED,
NOLDBA_SUPPORT_ORDER.GLOBAL_BEG
FROM NOLDBA_SUPPORT_ORDER
WHERE (((NOLDBA_SUPPORT_ORDER.CASE)="20139847P") AND ((NOLDBA_SUPPORT_ORDER.TYPE)<>"V"))

RETURNS:

CASE...........ID.........TYPE...MED........GLOBAL_BEG
20139847P.2013984.....A.......C...........431203872
20139847P.CARDCFM....C...... A.......... 431580408

 

[Brianwarnock]

I would have to use 2 queries, some may do it with a sub query.

querymax
SELECT
Max(NOLDBA_SUPPORT_ORDER.GLOBAL_BEG) as maxofGlobal_Beg
FROM NOLDBA_SUPPORT_ORDER
WHERE (((NOLDBA_SUPPORT_ORDER.CASE)="20139847P") AND ((NOLDBA_SUPPORT_ORDER.TYPE)<>"V"))

then next query

SELECT
NOLDBA_SUPPORT_ORDER.CASE,
NOLDBA_SUPPORT_ORDER.ID,
NOLDBA_SUPPORT_ORDER.TYPE,
NOLDBA_SUPPORT_ORDER.MED,
NOLDBA_SUPPORT_ORDER.GLOBAL_BEG
FROM NOLDBA_SUPPORT_ORDER inner join querymax on NOLDBA_SUPPORT_ORDER.GLOBAL_BEG = querymax.MaxofGlobal_Beg

obviously untested air code.

Brian