How to get records from a table in a specific order ascending or descending

The following code example demonstrates how the results from a select query can be ordered and grouped.

While select myTable 

      order by Field1 asc, Field2 desc

{

   //...

}

While select  myTable 

       group by Field1 desc

{

   // ...

} 

See an example in custTable.lastPayment() 

CustTrans lastPayment()
{
    CustTrans   custTrans;

    select firstonly custTrans
        index hint AccountDateIdx
        order by TransDate desc, Voucher desc
        where custTrans.AccountNum  == this.AccountNum   &&
              custTrans.Invoice     == ''                &&
              custTrans.AmountCur   < 0;

    return custTrans;
}

You can also follow the example in  a report

boolean  fetch()
{
      QueryRun                tradeLoopTrans;

      tradeLoopTrans = new TradeLoopTrans(vendPurchOrderJour,        tablenum(VendPurchOrderTrans)).    buildQueryRun();
      vendFormletterDocument = VendFormletterDocument::find();
          

      tradeLoopTrans.query().dataSourceTable(

      tablenum(vendPurchOrderTrans)).addSortField(    fieldnum(vendPurchOrderTrans,bfpGroupId));

}