>This will be make two order by with two fields as independent:
>select TOP 1 CONVERT(VARCHAR(10),DATE_FLD,101) + STR(ORDER_NO), FIELD2, FIELD3 from MyTable order by DATE_FLD, ORDER_NO DESC
>
>Try this one:
>select TOP 1 CONVERT(VARCHAR(10),DATE_FLD,101) + STR(ORDER_NO), FIELD2, FIELD3 from MyTable order by 1 DESC
Is this VFP or TSQL? Because convert(..., 101) looks like TSQL, and I'm not sure „order by 1“ is allowed in TSQL. Last time I tried you had to repeat the whole expression in the order by clause.
Also, I'd make the str() use fixed with, so not to make 101 come before 2 in case the results are left aligned.
Other than that, yup, this would work, but then why not
select top 1 date_fld, order_no, field2, field3 order by date_fld desc, order_no