| From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Join tables by nearest date? |
| Date: | 2009-07-28 07:33:54 |
| Message-ID: | 20090728073354.GB1868@a-kretschmer.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Devrim GÜNDÜZ | 2009-07-28 07:52:26 | Re: For production: 8.4 or 8.3? |
| Previous Message | Magnus Hagander | 2009-07-28 07:28:32 | Re: Postgres 8.2 database recovery Could not create relation Invalid Argument |