ORDER BY is used to sort the ordering of results in a resultset from an operation (usually select).

Best Practice on ORDER BY

It is generally considered a best practice to have an index on each column contained in an order by statement. This will ensure the fastest possible sort time for the ordering. Do not create indexes that duplicate the column from a compound index. If you have a column as the first column in a compound index it can be used for single index matching as well.

Aggregate Functions

ORDER BY in VistaDB may not use aggregates or computed columns like DATEPART(day, col) or SUM(col). Instead use the column index or name from the select statement.

As an example look at this SQL Server statement:

SELECT 
    ISNULL(sum(totalpaid),0) as DailyTotal, 
    ISNULL(count(invoiceid),0) as Invoices, 
    DATEPART( day, createddate) as SaleDay, 
    DATEPART( month, createddate) as SaleMonth, 
    DATEPART( year, createddate) as SaleYear
FROM invoices    
WHERE createddate >= @startdate and createddate <= @enddate and Void <> 1
GROUP BY DATEPART( day, createddate ), DATEPART(month, createddate), DATEPART( year, CreatedDate)   
ORDER BY DATEPART( year, CreatedDate) , DATEPART(month, createddate), DATEPART( day, createddate )

This code is summarizing a sales table (showing 0 if the sum is null for the date), the number of invoices (showing 0 if none), then splitting up the date into a SaleDay, SaleMonth, SaleYear. This syntax is not supported in VistaDB, but you could rewrite the query as below and it will work in both environments.

SELECT
    ISNULL(sum(totalpaid),0) as DailyTotal, 
    ISNULL(count(invoiceid),0) as Invoices, 
    DATEPART( day, createddate) as SaleDay, 
    DATEPART( month, createddate) as SaleMonth, 
    DATEPART( year, createddate) as SaleYear
FROM invoices    
WHERE createddate >= @startdate AND createddate <= @enddate and Void <> 1
GROUP BY DATEPART( day, createddate ), DATEPART(month, createddate), DATEPART( year, CreatedDate)
ORDER BY SaleYear, SaleMonth, SaleDay

Notice the order by statement has been changed to use the column name rather than duplicating the functions used to build them. Also note that you CANNOT do this for the group by block.