Thread: Selecting the most recent date

Hi,

My question is hopefully a simple one:  If I have several rows, each containing a date field, and I want to select the
rowthat has the date closest to today's date, what would be the syntax for carrying that out. 

--
Thanks,

Mark

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

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:
> Hi,
>
> My question is hopefully a simple one:  If I have several rows, each containing a date field, and I want to select
therow that has the date closest to today's date, what would be the syntax for carrying that out. 
>

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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

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