+ Start a Discussion
cwoodcwood 

Implicit ORDER BY for SOQL queries?

If I specify a batch size of 25 and then perform a query like "SELECT FirstName, LastName from contact", will I get the 25 most recently created contacts?

If not, which contacts will I get? And if not, how can I query for the 25 most recently created contacts?

Thanks,
Charlie

Message Edited by cwood on 04-05-2006 10:17 AM

Message Edited by cwood on 04-05-2006 10:17 AM

SuperfellSuperfell
There's no deterministic order, its whatever the SQL optimizer lands on. ORDER BY and a bunch of other SOQL improvements are being planned.

cwoodcwood
OK thanks. So it sounds like the only way to determine the 25 most recently created contacts is by doing a query like

SELECT FirstName, LastName FROM contacts WHERE CreatedDate > (7 days ago)

then checking to see that at least 25 were returned. If fewer than 25 were returned, we expand the date range and do a query like

SELECT FirstName, LastName FROM contacts WHERE CreatedDate > (14 days ago)

looping until we get >= 25 results. Once we have at least 25 results, we sort the returned objects on CreatedDate, and then use the 25 most recent.

Does that sound right? If so, I can't wait for ORDER BY. :-)

-Charlie
darozdaroz


cwood wrote:
OK thanks. So it sounds like the only way to determine the 25 most recently created contacts is by doing a query like

SELECT FirstName, LastName FROM contacts WHERE CreatedDate > (7 days ago)

then checking to see that at least 25 were returned. If fewer than 25 were returned, we expand the date range and do a query like

SELECT FirstName, LastName FROM contacts WHERE CreatedDate > (14 days ago)

looping until we get >= 25 results. Once we have at least 25 results, we sort the returned objects on CreatedDate, and then use the 25 most recent.

Does that sound right? If so, I can't wait for ORDER BY. :-)

-Charlie




One thing to note with that solution. There may be a point at which doing a larger query, including the CreatedDate, and then sorting the results if > 25 may be faster then stepping up thru multiple queries. Depending on your usage you may want to find a query that returns ~100 records or so and sort them to get your 25.
cwoodcwood
Yeah, but you might have to experiment--at runtime--with multiple queries to work to find a query that returns more than 25 and less than 2000. ORDER BY will be a good thing.

Thanks,
Charlie
bildbild
This would be an EXTREMELY useful feature for anyone who doesn't want to have to drag (potentially) hundreds or thousands of records across the net to find the most recent one.

I am waiting with great antici...



...pation. :)

mhamberg1mhamberg1
Do we know a timeframe on the ORDER BY clause? If I can't sort like that, my results are going to be a mess.

Thanks