Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TRg9q-0006UG-UL for pgsql-sql@postgresql.org; Fri, 26 Oct 2012 09:24:51 +0000 Received: from hub.ringways.co.uk ([77.86.27.30] helo=mail.ringways.co.uk) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TRg9p-00031y-Jg for pgsql-sql@postgresql.org; Fri, 26 Oct 2012 09:24:50 +0000 Received: from localhost ([127.0.0.1] helo=mail.ringways.co.uk) by mail.ringways.co.uk with esmtp (Exim 4.69) (envelope-from ) id 1TRg9k-00066F-Va for pgsql-sql@postgresql.org; Fri, 26 Oct 2012 10:24:46 +0100 Received: from eddie.ringways.co.uk ([10.1.1.115] helo=eddie.ringways.co.uk) by mail.ringways.co.uk with ESMTP id qAOc9C0s06686; Fri, 26 Oct 2012 10:24:38 +0100 From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: Re: pull in most recent record in a view Date: Fri, 26 Oct 2012 10:24:39 +0100 User-Agent: KMail/1.9.10 References: <201210261002.02001.gary.stainburn@ringways.co.uk> In-Reply-To: <201210261002.02001.gary.stainburn@ringways.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <201210261024.40026.gary.stainburn@ringways.co.uk> X-SpamTest-Envelope-From: gary.stainburn@ringways.co.uk X-SpamTest-Info: Profiles 20401 [Mar 31 2011] X-SpamTest-Method: none X-SpamTest-Rate: 0 X-SpamTest-Status: Not detected X-SpamTest-Status-Extended: not_detected X-SpamTest-Version: SMTP-Filter Version 3.0.0 [0285], KAS30/SDK/Release X-Anti-Virus: Kaspersky Mail Gateway, version: 5.6.28/RELEASE, bases: 20110331T110535 #5151353, check: 20121026 clean X-Spam-Score: -51.6 (---------------------------------------------------) X-Spam-Report: Spam detection software, running on the system "ollie.ringways.co.uk", has identified this incoming email as possible spam. The original message has been attached to this so you can view it (if it isn't spam) or label similar future email. If you have any questions, see Gary Stainburn for details. Content preview: 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; [...] Content analysis details: (-51.6 points, 15.0 required) pts rule name description ---- ---------------------- -------------------------------------------------- -50 ALL_TRUSTED Passed through trusted hosts only via SMTP -2.6 BAYES_00 BODY: Bayesian spam probability is 0 to 1% [score: 0.0000] 0.0 AWL AWL: From: address is in the auto white-list 1.0 RING_SAFE RING_SAFE X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201210/55 X-Sequence-Number: 36926 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