From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pull in most recent record in a view |
Date: | 2012-10-28 14:54:12 |
Message-ID: | A20AAD55-F1D7-4E37-A500-EDE620DD6373@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Oct 26, 2012, at 5:24, Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:
> This is my best effort so far is below. My concern is that it isn't very
> efficient and will slow down as record numbers increase
>
> create view current_qualifications as
> select q.*, (q.qu_qualified+q.qu_renewal)::date as qu_expires from
> qualifications q
> join (select st_id, sk_id, max(qu_qualified) as qu_qualified from
> qualifications group by st_id, sk_id) s
> on q.st_id=s.st_id and q.sk_id = s.sk_id and q.qu_qualified = s.qu_qualified;
>
>
> select t.st_id, t.st_name, k.sk_id, k.sk_desc, q.qu_qualified, q.qu_renewal,
> q.qu_expires
> from current_qualifications q
> join staff t on t.st_id = q.st_id
> join skills k on k.sk_id = q.sk_id;
>
The best way to deal with recency problems is to maintain a table that contains only the most recent records using insert/update/delete triggers. A boolean flag along with a partial index can work instead of an actual table in some cases. If using a table only the pkid needs to be stored, along with any desired metadata.
It probably isn't worth the effort until you actually do encounter performance problems.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Fenbers | 2012-10-29 22:38:46 | Fun with Dates |
Previous Message | Oliveiros d'Azevedo Cristina | 2012-10-28 11:15:27 | Re: complex query |