>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

SELECT MIN(name) name, master_code FROM mytable ;
GROUP BY master_code;
HAVING COUNT(*) = SUM(IIF(master_ind = 'Y',1,0))