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