Selecting the most recent date

select (rows desired)
from tableselect
where abs( todays_date - col_date) = min( abs( todays_date - col_date));

SORT OF <LOL>, I'm not sure how to find that particular row, but is how you find the minimum difference from todays date.

Mark Tessier wrote:

that assumes all dates are older than today, which they man not be. What if it's for an appointment system?

Jay O'Connor wrote:

On 2003.04.28 10:26 Mark Tessier 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.

Unless I misunderstand, it should be just a matter of an ORDER BY clause in
reverse order with a LIMIT BY clause to limit yuorself to the highest
result

SELECT * from myyable ORDER BY datefield DESC LIMIT 1;

DESC means to using a descending sorta order (versus "ASC")
LIMIT 1 means how many rows you want returned

Take care,
Jay

--- 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;

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

On Mon, 28 Apr 2003 13:26:55 -0400
Mark Tessier <m_tessier@sympatico.ca> wrote:

Thanks for all your help.

Mark

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

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