Thanks Naomi. It seems to work. The statement is a little slow in generating the result set but I'll work on speeding it up.
Tim
>>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
>