Re: Improve a query...

On Wed, May 02, 2001 at 01:17:44AM -0400, Tom Lane wrote:
> "Eric G. Miller" <egm2(at)jps(dot)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(at)jps(dot)net>