Re: How to Select a Tupl by Nearest Date

Thanks for your solution -  it works (with some slightly adaptions) - but its a bit slow ...

select * from ((select id, foodate-'2008-07-06'::date as difference from foo where foodate > '2008-07-06'::date --
forgota order by foodate limit 1)  union(select id, '2008-07-06'::date-foodate as difference from foo where foodate
<'2008-07-06'-- forgot a order by foodate desc limit 1) 
 
) bar 
order by 2 asc limit 1

I think the Problems are the order bys (3 times) and the union. So I took your approach and thought about a max/min
solution:

select id from foo f, (select max(foodate) as foodate from foo where foodate < '2008-07-06'::date) as mmin,(select
min(foodate)as foodate from foo where foodate > '2008-07-06'::date) as mmax
 
where case when '2008-07-06' - mmin.foodate > mmax.foodate - '2008-07-06' then f.foodate = mmax.foodateelse f.foodate =
mmin.foodate     end
 
limit 1

on 500'000 rows the first query needs 312 ms (cached) and the second query needs 219 (also cached) so its a bit faster
butI hoped there is a special postgres function which will be faster like a single function call (just the max function
needsonly 141ms) ... 
 

Thanks for your Help
Christian




-------- Original-Nachricht --------
> Datum: Tue, 22 Jul 2008 11:06:07 +0200
> Von: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] 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
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

-- 
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser