From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: pull in most recent record in a view
Date: Fri, 26 Oct 2012 10:02:01 +0100
Message-ID: <201210261002.02001.gary.stainburn@ringways.co.uk> (raw)
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
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: <201210261002.02001.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