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

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

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

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