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>