>I installed VFP9, checked documentation and couldn't find anything that explain it as 'not a bug'. Tested this code and got 'Order by clause is invalid'. Why?
>
>
select distinct city,right(Rtrim(city),3) as country ;
> from (_samples+'data\customer') ;
> order by country
>
1. Look into ORDER BY help topic. There is a list of "things", which can be used as an Order_Item, listed from highest precedence to lowest precedence. Field from a table is the first item in the list - highest precedence.
2. Look into SET ENGINEBEHAVIOR topic.
"A SELECT … DISTINCT … ORDER BY command will generate an error if the specified ORDER BY field is not in the SELECT field list."
3. Look on the query. According to documentation, it should fail because customer.country is not in SELECT list.
>This is the only VFP version I get this error with a query like this.
>
Well, if you like results like this, you can change ENGINEBEHAVIOR setting appropriately.
CLEAR
?VERSION()
CREATE CURSOR test (f1 I, f2 I)
INSERT into test values (1,4)
INSERT into test values (1,3)
INSERT into test values (2,1)
INSERT into test values (2,0)
INSERT into test values (3,2)
INSERT into test values (3,8)
SELECT DISTINCT f1 as f2 from test ORDER BY f2 into cursor result
LIST
RETURN
Visual FoxPro 08.00.0000.2521 for Windows
Record# F2
1 2
2 3
3 1
Thanks,
Aleksey.