On Oct 26, 2012, at 5:24, Gary Stainburn <gary.stainb...@ringways.co.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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
