Re: Join tables by nearest date?

On Mon, Jul 27, 2009 at 10:51:00PM -0700, Nick wrote:
> Is it possible to join two tables by the nearest date? For example how
> could I find out where the user was on their birthday?

DISTINCT ON[1] is normally the easiest way:

  SELECT DISTINCT ON (u.id) u.id, l.id, l.created
  FROM users u, users_locations l
  WHERE u.id        = l.user_id
    AND u.birthday <= l.created
  ORDER BY u.id, l.created

Untested, but hopefully gives enough hints about where to look!

--
  Sam  http://samason.me.uk/

 [1] http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT