Postgres Pro
Downloads
Home   >   mailing lists

Thread: Join tables by nearest date?

Join tables by nearest date?

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

Re: Join tables by nearest date?

From
"A. Kretschmer"
Date:
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

Re: Join tables by nearest date?

From
Sam Mason
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

Re: Join tables by nearest date?

From
"A. Kretschmer"
Date:
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