Tuesday, July 22, 2008

[SQL] How to Select a Tupl by Nearest Date

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 :)

Thanks
Christian
--
GMX Kostenlose Spiele: Einfach online spielen und Spaß haben mit Pastry Passion!
http://games.entertainment.gmx.net/de/entertainment/games/free/puzzle/6169196

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

No comments: