pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpull in most recent record in a view
3+ messages / 2 participants
[nested] [flat]
* pull in most recent record in a view
@ 2012-10-26 09:02 Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-10-26 09:24 ` Re: pull in most recent record in a view Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2012-10-26 09:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pull in most recent record in a view
2012-10-26 09:02 pull in most recent record in a view Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2012-10-26 09:24 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-10-28 14:54 ` Re: pull in most recent record in a view David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2012-10-26 09:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pull in most recent record in a view
2012-10-26 09:02 pull in most recent record in a view Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-10-26 09:24 ` Re: pull in most recent record in a view Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2012-10-28 14:54 ` David Johnston <polobo@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Johnston @ 2012-10-28 14:54 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-10-28 14:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-26 09:02 pull in most recent record in a view Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-10-26 09:24 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-10-28 14:54 ` David Johnston <polobo@yahoo.com>
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