Обсуждение: Join tables by nearest date?
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? users (id, name, birthday) 1 | one | 2009-07-27 2 | two | 2009-07-28 3 | three | 2009-07-29 4 | four | 2009-07-30 5 | five | 2009-07-31 users_locations (id, user_id, created) 1 | 1 | 2009-05-21 2 | 1 | 2009-06-21 3 | 1 | 2009-07-21 4 | 2 | 2009-05-10 5 | 2 | 2009-06-10 6 | 2 | 2009-07-10
In response to Nick : > 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? > > users (id, name, birthday) > 1 | one | 2009-07-27 > 2 | two | 2009-07-28 > 3 | three | 2009-07-29 > 4 | four | 2009-07-30 > 5 | five | 2009-07-31 > > users_locations (id, user_id, created) > 1 | 1 | 2009-05-21 > 2 | 1 | 2009-06-21 > 3 | 1 | 2009-07-21 > 4 | 2 | 2009-05-10 > 5 | 2 | 2009-06-10 > 6 | 2 | 2009-07-10 test=*# select distinct on (u.id) u.name, u.birthday, l.created, u.birthday - l.created as diff from users u right join users_locations l on (u.id=l.user_id) order by u.id, diff; name | birthday | created | diff ------+------------+------------+------ one | 2009-07-27 | 2009-07-21 | 6 two | 2009-07-28 | 2009-07-10 | 18 (2 rows) Helps that? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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
In response to Sam Mason : > 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 AND u.birthday >= l.created Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net