[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] pull in most recent record in a view
From:       David Johnston <polobo () yahoo ! com>
Date:       2012-10-28 14:54:12
Message-ID: A20AAD55-F1D7-4E37-A500-EDE620DD6373 () yahoo ! com
[Download RAW message or body]

On Oct 26, 2012, at 5:24, Gary Stainburn <gary.stainburn@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


[prev in list] [next in list] [prev in thread] [next in thread]