Hi all,

I'm trying to clean up a GROUP BY query so that it will work with VFP9 (using ENGINEBEHAVIOR 90). I need to group by a single field (SERV), while including another as a descriptor (DESC1). The second field may have slightly different spelling across similar records and I don't really care which is included in the result.

Starting table: dtparat.dbf (PK not shown, SERV and DESC1 repeat across different BTYPE categories although there may be some odd spellings and a special SERV thrown in here and there to make it interesting)

BTYPE  SERV  DESC1

DPH    TA    Home visit
DPH    TB    Group visit
DPH    TC    Parent visit
DPH    TD    Special visit -DPH only
MED    TA    Home visit
MED    TB    Grp. visit
MED    TC    Parent vis.
OTH    TA    Home visit
OTH    TB    Group visit
OTH    TC    Parent visit

Desired result:

SERV   DESC1

TA     Home visit
TB     Group visit (or Grp. visit)
TC     Parent visit (or Parent vis.)
TD     Special visit - DPH only

Old query:

select serv, desc1;
  from dtparat;
  group by serv  && works with ENGINEBEHAVIOR 70, not 90

I can't include DESC1 in the GROUP BY clause as required by 90, because some of the values are spelled slightly differently, resulting in multiple lines for a given SERV value. I've tried various nested joins with a DISTINCT intermediate file with no luck, such as:

SELECT dtparat.serv, dtparat.desc1 ;
  FROM dtparat;
  RIGHT JOIN (select DISTINCT serv;
          FROM dtparat;
          GROUP BY serv) distserv;
  ON dtparat.serv = distserv.serv;
  ORDER BY dtparat.serv  && gives too many records

There's gotta be a way to do this in one step (to use as a view). Any help would be much appreciated.

TIA,