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

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

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!

Rob N. 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

On 14 July 2010 15:22, Max W. [email protected] wrote:

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

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

On 14 July 2010 16:44, Max W. [email protected] wrote:

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).

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

Colin L. wrote:

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

In my experience, SQL is always faster than Ruby. If you need to improve
the performance of your Ruby application one easy way is to do fewer SQL
calls and to get the SQL engine to do more of the work. One complex SQL
call is almost always quicker than two or three simpler calls.

I expect the difference in performance between doing the one complex SQL
call and just the first of Max’s original statements (the case where
that one returns a result, and therefore the second ‘or’ statement isn’t
called), is minimal. Unless your SQL call is particularly complex or
poorly designed, its not SQL that slows the data retrieval in
ActiveRecord.

However, the trade off is maintainability. Two or three simple steps are
usually easier to write and easier to debug. They are also easier for a
third party to understand.

So now it’s Max’s choice - best performance or more easily maintained.
Personally, I’d go for his original solution, unless there was a
performance issue.

Oh! and to maximise the maintainability, I’d recommend splitting the
queries out to their own methods:

def last_state
self.states.find(:first, :order => “created_at”)
end

def first_state_after(time)
self.states.find(:first, :conditions => [“created_at < ?”, time],
:order => “created_at desc”)
end

first_state_after(time) || last_state

On 15 July 2010 08:50, Rob N. [email protected] wrote:

:order => “created_at desc”)
end

first_state_after(time) || last_state

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

Colin L. 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)

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs