FPD25b SQL SELECT filter subset of a query result based on date
Hi all,

This is my SQL following:

	SELECT * ;
		FROM a25mcust mcust, a25scust scust, a25mpckg mpckg, a25spckg spckg, ;
			a25mchnl mchnl, a25mdeco mdeco, a25sdeco sdeco ;
		WHERE scust.docid = mcust.uniqueid AND ;
			scust.packageid = mpckg.uniqueid AND ;
			spckg.docid = mpckg.uniqueid AND ;
			spckg.channelid = mchnl.uniqueid AND ;
			sdeco.docid = mdeco.uniqueid AND ;
			mdeco.channelid = mchnl.uniqueid AND ;
			sdeco.custid = mcust.uniqueid ;
		ORDER BY mcust.company, mcust.ccity, scust.applicdt, mchnl.channel ;
		INTO CURSOR custcomplete

there will be multiple records to a25scust for each a25mcust. Now all records in a25scust for a given a25mcust can also be grouped by one of the fields, ie. a25scust.applicdt.

Now my problem starts:
I only want to show the latest group of a25scust.applicdt in the above query, ie. the older group of a given a25mcust are not required to be shown. The subset is latest and not for a given date or period.

Anyway I can filter the above query's result? Programmatically this is possible but I want to use SQL as much as possible. I dont mind intermediate SQLs. Cause I want to give the end result straight to the Report Form thus keeping it flexible for the user to select any number of fields.

One thing I tried unsuccessfully was the following query and insert in the above query WHERE clause:

	scust.packageid IN (SELECT sc.packageid DISTINCT ;
		FROM a25scust sc ;
		ORDER BY sc.docid, sc.applicdt DESC) AND ;

plus i kept receiving ) missing error


Thanks for any help in advance

Regards
Bhavbhuti