I am doing a query on two tables. Multiple records are found in the child table based on the primary key of the master table. I need to only keep one record from the child table. That record is the one on top of the ORDER BY.

If I don't include the GROUP BY, the record I want to keep is always at the top. However, as I only need to keep one record from the child record, I have to put a GROUP BY. When I put, it doesn't take the top record.

SELECT MASTER.MASTERKEY,CHILD.DATE AS CHILDDATE FROM CHILD;
INNER JOIN MASTER ON CHILD.MASTERKEY=MASTER.MASTERKEY;
GROUP BY CHILD.MASTERKEY ORDER BY CHILD.DATE DESCENDING;
INTO CURSOR TEMP

If I don't put the GROUP BY, I'll have something like this:

000001 01/01/2000
000001 12/12/1999
000001 05/06/1999
000001 05/05/1999
000002 01/01/2000
000002 12/11/1999
000002 10/10/1999
000002 09/09/1999

If I put the GROUP BY, I'll have something like this:

000001 05/05/1999
000002 09/09/1999