From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: pull in most recent record in a view
Date: Fri, 26 Oct 2012 10:24:39 +0100
Message-ID: <201210261024.40026.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <201210261002.02001.gary.stainburn@ringways.co.uk>
References: <201210261002.02001.gary.stainburn@ringways.co.uk>
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;
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: pull in most recent record in a view
In-Reply-To: <201210261024.40026.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox