Skip site navigation (1) Skip section navigation (2)

How to Select a Tupl by Nearest Date

From: "Christian Kindler" <christian(dot)kindler(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: How to Select a Tupl by Nearest Date
Date: 2008-07-22 08:42:56
Message-ID: 20080722084256.302230@gmx.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: A. KretschmerDate: 2008-07-22 09:06:07
Subject: Re: How to Select a Tupl by Nearest Date
Previous:From: Scott MarloweDate: 2008-07-22 07:28:51
Subject: Re: PERSISTANT PREPARE (another point of view)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group