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
-- forgot a
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.foodate
else
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 but I hoped there is a special postgres function which will be faster like a single function call (just the max function needs only 141ms) ...

Thanks for your Help
Christian

-------- Original-Nachricht --------
> Datum: Tue, 22 Jul 2008 11:06:07 +0200
> Von: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
> An: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)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