Re: newbie question for return date

Hi guys, here a newbies question.
I've made a table with some action and date.
How may i query the most recent date. (I'd like to return only the record
which have the most recent date ).
Tx

In article <xEgv9(dot)2668$h_4(dot)374526(at)news20(dot)bellglobal(dot)com>, tviardot wrote:
> Hi guys, here a newbies question.
> I've made a table with some action and date.
> How may i query the most recent date. (I'd like to return only the record
> which have the most recent date ).
> Tx
>
>

How about:

SELECT * FROM t ORDER BY d DESC LIMIT 1;

Hey a suggestion, what if PG would support the negative limit as in
select * from table limit -1 to mean limit it from the other end of
list.....

Sort of like some languages where they support

array[1] vs array[-1].

I'm not sure, but it looks like order by will sort the list which is
expensive and then
allow us to get the first chunk specified by limit.

Lee Harr wrote:

>In article <xEgv9(dot)2668$h_4(dot)374526(at)news20(dot)bellglobal(dot)com>, tviardot wrote:
>
>
>>Hi guys, here a newbies question.
>>I've made a table with some action and date.
>>How may i query the most recent date. (I'd like to return only the record
>>which have the most recent date ).
>>Tx
>>
>>
>>
>>
>
>How about:
>
>SELECT * FROM t ORDER BY d DESC LIMIT 1;
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>

In order for your proposed result of array[-1] to be consistent/predictable,
an ORDER BY sorting action will have to be performed *anyway* (in order to
consistently return the last record(s) from the recordset), so doing an
ORDER BY field_name DESC LIMIT 1 will have the same effect.

Just my 2 cents

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Medi Montaseri
> Sent: Monday, October 28, 2002 8:13 PM
> To: missive(at)hotmail(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] newbie question for return date
>
>
> Hey a suggestion, what if PG would support the negative limit as in
> select * from table limit -1 to mean limit it from the other end of
> list.....
>
> Sort of like some languages where they support
>
> array[1] vs array[-1].
>
> I'm not sure, but it looks like order by will sort the list which is
> expensive and then
> allow us to get the first chunk specified by limit.
>
> Lee Harr wrote:
>
> >In article <xEgv9(dot)2668$h_4(dot)374526(at)news20(dot)bellglobal(dot)com>,
> tviardot wrote:
> >
> >
> >>Hi guys, here a newbies question.
> >>I've made a table with some action and date.
> >>How may i query the most recent date. (I'd like to return
> only the record
> >>which have the most recent date ).
> >>Tx
> >>
> >>
> >>
> >>
> >
> >How about:
> >
> >SELECT * FROM t ORDER BY d DESC LIMIT 1;
> >
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> >message can get through to the mailing list cleanly
> >
> >
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>

Medi Montaseri <medi(dot)montaseri(at)intransa(dot)com> writes:
> Hey a suggestion, what if PG would support the negative limit as in
> select * from table limit -1 to mean limit it from the other end of
> list.....

You'd hardly want that, as it would necessarily be the slowest possible
way of retrieving the rows you're after.

Instead, reverse the sort order. For example

select * from table order by datecol desc limit 1;

regards, tom lane

On Monday 28 Oct 2002 7:57 pm, tviardot wrote:
> Hi guys, here a newbies question.
> I've made a table with some action and date.
> How may i query the most recent date. (I'd like to return only the record
> which have the most recent date ).

SELECT * from table_name ORDER BY date_column DESC LIMIT 1;

If you have an index on date_column this will be almost instant. If two rows
have the same (largest) value in date_column which gets returned isn't well
defined.

--
Richard Huxton