Thread: Fwd: Re: Selecting the most recent date
--- Jeff Eckermann <jeff_eckermann@yahoo.com> wrote: > Date: Mon, 28 Apr 2003 12:07:00 -0700 (PDT) > From: Jeff Eckermann <jeff_eckermann@yahoo.com> > Subject: Re: [GENERAL] Selecting the most recent > date > To: Mark Tessier <m_tessier@sympatico.ca>, > pgsql-general@postgresql.org > > --- Mark Tessier <m_tessier@sympatico.ca> wrote: > > Hi, > > > > My question is hopefully a simple one: If I have > > several rows, each containing a date field, and I > > want to select the row that has the date closest > to > > today's date, what would be the syntax for > carrying > > that out. > > > select * from tablename order by abs(current_date - > datefield) desc limit 1; > Whoops, that "desc" should not be there: that would get you the opposite result to the one you want ;-) Sorry for the (my) confusion. __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com
actually, that might be what he wants, all the datefields JUST past, and all the date fields JUST ahead Jeff Eckermann wrote: > --- Jeff Eckermann <jeff_eckermann@yahoo.com> wrote: > >>Date: Mon, 28 Apr 2003 12:07:00 -0700 (PDT) >>From: Jeff Eckermann <jeff_eckermann@yahoo.com> >>Subject: Re: [GENERAL] Selecting the most recent >>date >>To: Mark Tessier <m_tessier@sympatico.ca>, >> pgsql-general@postgresql.org >> >>--- Mark Tessier <m_tessier@sympatico.ca> wrote: >> >>>Hi, >>> >>>My question is hopefully a simple one: If I have >>>several rows, each containing a date field, and I >>>want to select the row that has the date closest >> >>to >> >>>today's date, what would be the syntax for >> >>carrying >> >>>that out. >>> >> >>select * from tablename order by abs(current_date - >>datefield) desc limit 1; >> > > Whoops, that "desc" should not be there: that would > get you the opposite result to the one you want ;-) > Sorry for the (my) confusion. > > __________________________________ > Do you Yahoo!? > The New Yahoo! Search - Faster. Easier. Bingo. > http://search.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
--- Dennis Gearon <gearond@cvc.net> wrote: > actually, that might be what he wants, all the > datefields JUST past, and all the date fields JUST > ahead The specification was ambiguous: I took "closest to" to mean "shortest distance away", i.e. in any direction. > > Jeff Eckermann wrote: > > --- Jeff Eckermann <jeff_eckermann@yahoo.com> > wrote: > > > >>Date: Mon, 28 Apr 2003 12:07:00 -0700 (PDT) > >>From: Jeff Eckermann <jeff_eckermann@yahoo.com> > >>Subject: Re: [GENERAL] Selecting the most recent > >>date > >>To: Mark Tessier <m_tessier@sympatico.ca>, > >> pgsql-general@postgresql.org > >> > >>--- Mark Tessier <m_tessier@sympatico.ca> wrote: > >> > >>>Hi, > >>> > >>>My question is hopefully a simple one: If I have > >>>several rows, each containing a date field, and I > >>>want to select the row that has the date closest > >> > >>to > >> > >>>today's date, what would be the syntax for > >> > >>carrying > >> > >>>that out. > >>> > >> > >>select * from tablename order by abs(current_date > - > >>datefield) desc limit 1; > >> > > > > Whoops, that "desc" should not be there: that > would > > get you the opposite result to the one you want > ;-) > > Sorry for the (my) confusion. > > > > __________________________________ > > Do you Yahoo!? > > The New Yahoo! Search - Faster. Easier. Bingo. > > http://search.yahoo.com > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com