November 10, 2016 by LanceShi
2.4 Use order by, limit and offset
Suppose we want to get the 11-20th most expensive merchandise from our database, how should we query that?
Don’t worry. It is not as hard as it seems. Below is the query:
Select Id, Name, Original_Price__c From Merchandise__c Order by Original_Price__c desc Limit 10 Offset 10
Let’s analyse the query.
Order by keyword is coming after the where clause. It means the result should come in a sorted manner. Asc means ascending order and Desc means descending order. In our previous code, it is descending order which means most expensive ones are on the top.
Limit key word means the number of records returned. If without offset keyword, it is always the top n records.
Offset means skipping some of the records from 1 on. E.g. offset 10 means skipping 10 records.
A bit more explanation
With all these grammars prepared, the query doesn’t seem to be too hard now, right? We sort the list by original price in a descending order, then we get the first 10 records after skipping the first 10 records, which means we are actually getting 11-20th.
Get the first 10 transactions whose customer name is John and order by its merchandise’ original price.