+ Start a Discussion
pradyprady 

SOQL Help

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.

 

Thanks

 

 

 

anyioneta1.3025054394678596E12anyioneta1.3025054394678596E12

Try This

 

SELECT Id, scantime__c, name, asset__c, status from trans__c

WHERE status = ‘pending’ ORDER BY scantime ASC LIMIT 1;

 

 

 

 

pradyprady

Limit 1 will only show one record.. What i wanted was

 

to get records of all assets, but if there are assets which are repeating then i only want the latest record of the repeating asset.

 

For eg.

if there are 3 records

asset         name      scantime

1              Rec 1      17-dec-2011 13:10

1              Rec2       17-dec-2011 13:50

2              Rec3        17-dec-2011 13:10

The output of the query should be

1              Rec2       17-dec-2011 13:50

2              Rec3        17-dec-2011 13:10

 

 

 

Thanks

 

 

Starz26Starz26

try

Set<External ID type> setOfIDs = New Set<External ID Type>();

trans__c[] tcList = New trans__c[]{};

 

for (trans__c t : [SELECT Id, scantime__c, name, asset__c, status from trans__c

WHERE status = ‘pending’ ORDER BY scantime__c DESC]){

 

   if(!setOFIDs.contains(assett__c)){

      setOfIDs.add(t.assett__c);

       tcList.add(t);

   }

}

 

You should end up with a list (tcList) of unique assest with any duplicated asset only showing the latest scantime.

       

kiranmutturukiranmutturu

if you go with desc in the query then u will access the latest in all those...

 

[select id from objectname order by field name desc]

Starz26Starz26

See my post above.

 

If you do not filter the query furthur you do not meet the use case of not returning any duplicate asset__c records. While sorting DESC will return the correct order, you still have the issue of multiple records for the same assett__c