How to use order by in Big Object Query?

Question

I am trying to query records from big object in a list, using the following query

Select Edit_Date__c ,CreatedDate,Field__c ,Loan_ID__c ,New_Value__c ,Old_Value__c ,User__c, System_Updates__c, Audit_ID__c FROM Loan_Audit__b limit 1000

I want to return most recent created records in the list using the above query. The above query is not allowing me to add ORDER BY CreatedDate DESC so that i can sort the records as the latest created records appers first.Please provide me some solution or any workarounds to sort this records.

Answer 1

We can use Order By on the big object field, but this field should be a part of index and also it should be the first index key.

We can make any 5 field as index in big object. But the field using which you want to sort Order By the records in query should be the first index key field.

I have used the following query;

SELECT  CreatedById, CreatedDate, SystemModstamp, AlternateNumber__c, Blockname__c FROM BObject__b order by Blockname__c 

Here in this query, Blockname__c is my first index field.

Answer 2

You need to run your query as async SOQL and index the field you are querying. Aggregate functions are only supported then.

Implementation guide