Looking for the best way to formulate a query to select
the most "recent" entry for an organization in a table
like:
CREATE TABLE reports (
-- Report Id used to link up related 1:M rather than multi-key
rpt_id SERIAL NOT NULL PRIMARY KEY,
-- A Unique ID for the organization
org_id char(10) NOT NULL CHECK(CHARACTER_LENGTH(op_id) = 10),
-- The reporting period
period integer NOT NULL
-- Various and Sundry ...
.
.
.
UNIQUE (org_id,period)
);
If max(period) for an organization yields the most recent reporting,
I want to get whatever is the most recent report for each organization.
This query works, but seems expensive...
SELECT a.rpt_id, a.org_id, a.period, ...
FROM reports As a
INNER JOIN
(SELECT b.org_id, max(b.period) As period
FROM reports b group by b.org_id) As c
ON a.org_id = c.org_id and a.period = c.period;
EXPLAIN looks thusly:
NOTICE: QUERY PLAN:
Merge Join (cost=147.98..164.48 rows=10 width=48)
-> Sort (cost=69.83..69.83 rows=1000 width=32)
-> Seq Scan on reports a (cost=0.00..20.00 rows=1000 width=32)
-> Sort (cost=78.15..78.15 rows=100 width=16)
-> Subquery Scan c (cost=69.83..74.83 rows=100 width=16)
-> Aggregate (cost=69.83..74.83 rows=100 width=16)
-> Group (cost=69.83..72.33 rows=1000 width=16)
-> Sort (cost=69.83..69.83 rows=1000 width=16)
-> Seq Scan on reports b (cost=0.00..20.00
rows=1000 width=16)
The data is very hierarchical so I didn't want to carry around alot of
key fields in related "many-sided" tables which may also have related
"many-sided" tables. Any ideas on how to minimize the multiple
scans on the table? The numbers for explain probably aren't telling
much since there's not much real data in the table at this time...
--
Eric G. Miller <egm2@jps.net>