SELECT DISTINCT fieldId FROM tableId ORDER BY fieldId

The DISTINCT clause doesn't happen in Ax 2009 and earlier, maybe niether in the 2012 version. There is however a trick to obtain this funcionality.

Query getDistinctQuery(TableId _tableId, FieldId _fieldId)
{
    Query                   query;
    QueryBuildDataSource    qbdsUtilElements;
    QueryBuildFieldList     qbdsFieldList;
    ;

    query = new Query();
    qbdsUtilElements = query.addDataSource(_tableId);
    qbdsUtilElements.update(false);
    //qbdsUtilElements.addGroupByField(_fieldId);
    qbdsUtilElements.addSortField(_fieldId);
    qbdsUtilElements.orderMode(orderMode::GroupBy);
    qbdsFieldList = qbdsUtilElements.fields();
    qbdsFieldList.dynamic(false);
    qbdsFieldList.clearFieldList();        // >> COMPLETE WIERDENESS
    qbdsUtilElements.addSelectionField(_fieldId, SelectionField::Max);
    return query;
}

The SelectionField::Database doesn't work (nor do we know what it does) and I'm not really sure why we have to add the SelectionField enum in the first place. Without that optional SelectionField enum second parameter for some reason when the query is run and all of the table fields appear to be readded to the sql and we don't get the desired result.

Obviously the above function can be changed to include more fields.


Edit: 23/02/2012 
I'm revisiting this with a second example that does not require the above addSelectionField statement.

Below is the SQL that the query example will generate:

SELECT JournalNum, AccountNum, Due, PaymMode, TransDate, CurrencyCode 
FROM LedgerJournalTrans 
WHERE JournalNum = N'000004_008'
GROUP BY LedgerJournalTrans.JournalNum, LedgerJournalTrans.AccountNum, LedgerJournalTrans.Due, LedgerJournalTrans.PaymMode, LedgerJournalTrans.TransDate, LedgerJournalTrans.CurrencyCode

And the query example code:

    Query                   qry;
    QueryBuildDataSource    qbr1;
    QueryBuildRange         range;
    QueryBuildFieldList     qbdsFieldList;
    ;
    
    qry = new Query();
    qbr1 = qry.addDataSource(tablenum(LedgerJournalTrans));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, JournalNum));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, AccountNum));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, Due));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, PaymMode));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, TransDate));
    qbr1.addGroupByField(fieldnum(LedgerJournalTrans, CurrencyCode));

    range = qry.dataSourceTable(tablenum(LedgerJournalTrans)).findRange(fieldNum(LedgerJournalTrans, JournalNum));
    if (!range)
        range = qry.dataSourceTable(tablenum(LedgerJournalTrans)).addRange(fieldNum(LedgerJournalTrans, JournalNum));
    range.value('000004_08');

    qbdsFieldList = qbr1.fields();
    qbdsFieldList.dynamic(false);
    qbdsFieldList.clearFieldList();
    
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, JournalNum));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, AccountNum));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, Due));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, PaymMode));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, TransDate));
    qbdsFieldList.addField(fieldNum(LedgerJournalTrans, CurrencyCode));

    //element.query(qry);

    return qry;

Edit: 18/09/2014 
Finally let us not forget the quick-to-implement yet inefficient-to-run trick of using a collection class for holding unique values, a Set, to check if we have already treated the entity during a loop.

static void ACT_UniqueTest(Args _args)
{
    Set                 stAccountNums = new Set (Types::String);
    LedgerJournalTrans  ledgerJournalTrans;
    ;
    
    while select ledgerJournalTrans 
        where ledgerJournalTrans.AccountType == LedgerJournalACType::Cust
            && ledgerJournalTrans.JournalNum == '000002_017'
    {
        if (stAccountNums.in(ledgerJournalTrans.AccountNum))
            continue;
        stAccountNums.add(ledgerJournalTrans.AccountNum);

        info(strFmt('%1 - %2', 
            ledgerJournalTrans.AccountNum,
            CustTable::blocked(ledgerJournalTrans.AccountNum)));
    }
}