Re: Improve a query...

On Wed, May 02, 2001 at 01:17:44AM -0400, Tom Lane wrote:
> "Eric G. Miller" <egm2@jps.net> writes:
> > Looking for the best way to formulate a query to select
> > the most "recent" entry for an organization in a table
> > like:
>
> Take a look at the SELECT reference page's example for
> SELECT DISTINCT ON:
>
> : For example,
> :
> :         SELECT DISTINCT ON (location) location, time, report
> :         FROM weatherReports
> :         ORDER BY location, time DESC;
> :
> : retrieves the most recent weather report for each location.
>
> A tad nonstandard, but bloody effective, particularly if you
> have an appropriate index in place...

Tricky! After adding the unique 2 column index and using that construct
I get:

NOTICE:  QUERY PLAN:

Unique  (cost=1.20..1.22 rows=1 width=32)
  ->  Sort  (cost=1.20..1.20 rows=8 width=32)
          ->  Seq Scan on reports  (cost=0.00..1.08 rows=8 width=32)

EXPLAIN

--
Eric G. Miller <egm2@jps.net>