>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
>
>I only want the resulting set to include the name field that has the master_ind = "Y" when grouped on the master_code.
>
>This doesn't work:
>
>select name, master_code ;
> from company ;
> group by master_code ;
> order by name, master_ind desc
>
>I'm trying to get the result set to look like this:
>
>name master_code
>ABC Company 001
>DEF Company 002
>
>I have to use VFP 6.0 SP 5 for this. Any ideas?
>

>Tim

Try

select min(name) as Name, master_code ;
  from company ;
  where Master_Ind = 'Y' ;
  OR NOT EXISTS ;
(select 1 from Company Co where Co.Master_Code = Company.Master_Code and Co.Master_Ind = 'Y') ;
  group by master_code ;
  order by name, master_ind desc