I accidently saved this message before it was complete.

I'm trying to create a SQL Select statement that generates a query grouped on one field but ordered on another.

The records look like this:

name master_code master_ind
ABC Company 001 Y
ABC Comp 001 N
ABC Cmpny 001 N
DEF Comp 002 N
DEF Cmpny 002 N
DEF Company 002 Y
GHI Company 003 N
JKL Company 004 N

I only want the resulting set to include the name field that has the master_ind = "Y" when grouped on the master_code (when there are multiple records) but always include the name for records with only one master_code in a group which would always have a master_ind = "N".

I'm trying to get the result set to look like this:

name master_code
ABC Company 001
DEF Company 002
GHI Company 003
JKL Company 004

This doesn't work:

select name, master_code ;
from company ;
group by master_code ;
order by name, master_ind desc

I have to use VFP 6.0 SP 5 for this. Any ideas?

Tim