Thread: pull in most recent record in a view

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 

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 

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/deletetriggers.  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.