Re: How to Select a Tupl by Nearest Date

am Tue, dem 22.07.2008, um 10:42:56 +0200 mailte Christian Kindler folgendes:
> Hello
>
> Assume I have a table like
> create table foo (
> id serial,
> date foodate,
> primary key(id)
> );
>
> with 2 tupls
> insert into foo(foodate) values('2008-07-07'); --id = 1
> insert into foo(foodate) values('2008-07-04'); -- id = 2
>
> What I need is to select the nearest tupl by a given date and I do not know how to do this.
>
> Something like:
> select id from foo where foo date = nearest('2008-07-06');
> -> should return 1
>
> select id from foo where foo date = nearest('2008-07-05');
> -> should return 2
>
> How can I do this? Note: I have a large Table (> 5'000'000 rows) so a good performing way would be a welcome asset :)

Quick try:

test=*# select * from ((select id , foodate-'2008-07-06'::date as
difference from foo where foodate > '2008-07-06'::date limit 1) union
(select id, '2008-07-06'::date-foodate from foo where foodate <
'2008-07-06' limit 1)) bar order by 2 asc limit 1;
id | difference
----+------------
1 | 1
(1 row)

test=*# select * from ((select id , foodate-'2008-07-05'::date as
difference from foo where foodate > '2008-07-05'::date limit 1) union
(select id, '2008-07-05'::date-foodate from foo where foodate <
'2008-07-05' limit 1)) bar order by 2 asc limit 1;
id | difference
----+------------
2 | 1
(1 row)

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net