I've got a view that looks like this...

Select xtable.id from Xtable where xtable.id in (select min(xtable.id) from xtable group by xtable.foreignkey)

It's a view to return the first address for each person that has addresses. (Yep, likely not the best way to do this but its what I've been given to work with at the moment.)

Now for the funny part. This select statement works fine if you simply run it in the command window. And the view works fine if you just open the view.

But, if you try using the view designer or if you query the view with the nodata clause, fox returns an (invalid group by clause) error.

So far as I can tell its a perfectly legal construction, and the sub-select works fine by itself. And as mentioned the syntax works "most of the time".

I tried moving the select into the from clause and doing a join on it, but got the same behavior. I tried similar constructions with different tables in the main and sub selects (just to test the limits of the problem) but got the same results all around.

Anyone seen this wierdness and managed to overcome it?