SOQL query to retrieve records

Question

I need to query an object called trans__c which has the following fields

 id,
 scantime__c // datetime
 name
 asset__c // external id
 status

I need to get only data which has status as pending and if there are any repeating asset then I need to get only record with scantime as the latest.

For e.g.

If there are 3 records

   asset         name      scantime              Status

    1            Rec 1      17-dec-2011 13:10     Pending
    1            Rec2       17-dec-2011 13:50     Pending
    2            Rec3       17-dec-2011 13:10     Pending

The output of the query should be

    1            Rec2       17-dec-2011 13:50     Pending
    2            Rec3       17-dec-2011 13:10     Pending

Answer 1

Group By is only really for use with aggregate queries, which is a bit of a pain when it comes to the other fields that you don't want to aggregate.

This may not be the correct approach, but I'd grab all of the 'Pending' records then do the logic in code (assuming you're not masses of results):

map<integer, Trans__c> mapAssetToRecord = new map<integer, Trans__c>();

for(Trans__c [] sTransArr : [select Id, Name, Asset__c, Scan_Time__c, Status__c
                               from Trans__c
                              where Status__c = 'Pending'])
{
    for(Trans__c sTrains : sTransArr)
    {
        if(mapAssetToRecord.get(sTrans.Asset__c) == null)
        {
            mapAssetToRecord.put(sTrans.Asset__c, sTrans);
        }
        else if(sTrans.Scan_Time__c > mapAssetToRecord.get(sTrans.Asset__c).Scan_Time__c)
        {
            mapAssetToRecord.put(sTrans.Asset__c, sTrans);
        }
    }
}

// now mapAssetToRecord includes all of the records you want

There may be a more elegant solution involving some clever SOQL, but it escapes me right now (after all, it is Sunday morning and I've only just had breakfast!).