get last record before, or first record after, a given date?

rubyonrails-talk
1

Can anyone give me the sql for this? I can't quite figure it out. I want to find the most recent record from the states table that is before a given date, and if that doesn't exist, the first one *after* that date?

Currently i'm doing it as two seperate finds, like this, but i need to combine it into one sql call if i can.

      self.states.find(:first, :conditions => ["created_at < ?", time], :order => "created_at desc") ||         self.states.find(:first, :conditions => ["created_at > ?", time], :order => "created_at")

thanks, max

2

I think this SQL would do it:

SELECT * FROM `states` WHERE created_at < '2010-04-16'   OR created_at = (     SELECT created_at     FROM `states`     ORDER BY created_at ASC     LIMIT 1) ORDER BY created_at DESC LIMIT 1

But your original solution would be easier to maintain!

3

Rob Nichols wrote:

I think this SQL would do it:

SELECT * FROM `states` WHERE created_at < '2010-04-16'   OR created_at = (     SELECT created_at     FROM `states`     ORDER BY created_at ASC     LIMIT 1) ORDER BY created_at DESC LIMIT 1

But your original solution would be easier to maintain!

Thanks rob. It didn't occur to me that my fallback option will always just be the one with the lowest created_at, rather than the one after a given time (since we already established that there aren't any before the time with the first option).

cheers, max

4

Can anyone give me the sql for this? I can't quite figure it out. I want to find the most recent record from the states table that is before a given date, and if that doesn't exist, the first one *after* that date?

Do you mean date or datetime? In other words what about about any on the given date? If you mean datetime then there could still be one exactly at that time. Your spec says that this one should not be found, is that correct?

Colin

5

Hi colin - yes, sorry that was badly worded. Rob's solution is fine but for the record i meant 'datetime' and i should have said

"I want to find the most recent record from the states table before a given datetime, and if that doesn't exist, the first one *after* (or at) that datetime?"

cheers, max

6

Is there any advantage to this rather than the original solution (now simplified by the realisation that the second query does not need the :conditions spec)? It is all done in one query, but both parts of the query will be executed even when the second part is not required, I think.

Colin

7

Or possibly even better as named_scopes (and I think a typo on the names, they should be first_state, and last_state_before).

Colin Colin

8

Colin Law wrote:

even better as named_scopes (and I think a typo on the names, they should be first_state, and last_state_before).

Agreed on both points :o)