From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: pull in most recent record in a view
Date: Fri, 26 Oct 2012 10:02:01 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201210261002.02001.gary.stainburn@ringways.co.uk>

I know I've asked a similar question before but I can't find it.

I'm doing a new project for a charity which involves managing a skills / 
requesit matrix. For each skill type / staff id I need to keep a record of 
when the skill was aquired/renewed and when it expires.

Put simply

skills=# select * from staff;
 st_id | st_name 
-------+---------
     1 | Gary
(1 row)

skills=# select * from skills;
 sk_id | sk_desc | sk_renewal 
-------+---------+------------
     1 | Medical | 5 years
(1 row)

skills=# select * from qualifications ;
 st_id | sk_id | qu_qualified | qu_renewal 
-------+-------+--------------+------------
     1 |     1 | 2004-07-01   | 10 years
     1 |     1 | 2009-05-25   | 3 years
(2 rows)

skills=# 


What is the best (cleanest SQL or fastest performance) way to produce the 
following view?

 st_id | st_name | sk_id | sk_desc | last_qualified | Renewal | Expires
-------+---------+-------+---------+----------------|---------|-----------
     1 | Gary    |     1 | Medical | 2009-05-25     | 3 years | 2012-05-25


I've got the following which gives all but the last two fields. The problem is 
that the Renewal period and expires has to be from the most recent record, 
i.e. even though the record 1 above expires after record 2, the results of 
record 2 have to be used.

select t.*, k.sk_id, k.sk_desc, q.last_qualified from
(select st_id, sk_id, max(qu_qualified) as last_qualified from qualifications 
group by st_id, sk_id) q
join staff t on t.st_id = q.st_id
join skills k on k.sk_id = q.sk_id
order by st_id, sk_id

I am still at the concept stage for this project so I can change the schema if 
required

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk