Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d6Yf9-0007yN-4j for pgsql-sql@arkaria.postgresql.org; Fri, 05 May 2017 08:32:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d6Yf7-0003Be-ND for pgsql-sql@arkaria.postgresql.org; Fri, 05 May 2017 08:32:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d6Yf6-0003Ao-RE for pgsql-sql@postgresql.org; Fri, 05 May 2017 08:32:28 +0000 Received: from mout.gmx.net ([212.227.15.15]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d6Yf2-0007Nz-Ll for pgsql-sql@postgresql.org; Fri, 05 May 2017 08:32:27 +0000 Received: from hermes ([80.139.219.238]) by mail.gmx.com (mrgmx002 [212.227.17.190]) with ESMTPSA (Nemesis) id 0MV5tl-1dYLUJ1aSj-00YTJl for ; Fri, 05 May 2017 10:32:22 +0200 Received: from ncq by hermes with local (Exim 4.89) (envelope-from ) id 1d6Yez-0001vl-5H for pgsql-sql@postgresql.org; Fri, 05 May 2017 10:32:21 +0200 Date: Fri, 5 May 2017 10:32:21 +0200 From: Karsten Hilbert To: pgsql-sql@postgresql.org Subject: Re: Most recent row Message-ID: <20170505083220.wxdqm3bpwfo2dgre@hermes.hilbert.loc> References: <201705050925.04194.gary.stainburn@ringways.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <201705050925.04194.gary.stainburn@ringways.co.uk> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) User-Agent: NeoMutt/20170113 (1.7.2) X-Provags-ID: V03:K0:yOIpi1IOw9WBHxOs7I75hxf+7y3DFnFL6C06VNzgFPFN3ciSIWV mhBwT9dUVNHmQy1g7MW658yd7IDRU9X6OconoXD5FPqEtx6ISfaRhvWCpVgXkxyOFUPFJBg bilFr6FrwjrKqk0VlKoCE4YIacbmRBW8HaHRdlCmLZD0psR9MHa+K7iqKKbb45/zU7ncWLe dQjOqPOO9UBl3cdro+A5A== X-UI-Out-Filterresults: notjunk:1;V01:K0:YXafLeSBW2Q=:dWYrje/9PkmxGEgo56rCvz sD8huKMT8uYDJ1i3Kf/RFaV3J+iAbmVrdyTy9UrwUSPXR5KNb9YM22z7tq6u0vg0v6iJSvOJD fjXBzUeuxP6BstSzFjD0BcPL1kUhrA5a6RmA7q64IkcMNRpnW76UVpIST++veFZBC9cXLud6e fP8btKw3r1B6Tr/b7MaofHWAA27YuH4Fw3SnOM2r01yRR5+kMBDm06OTC04GuHuD87X8myDlI 6qaNOzQYwdSl/hCVxK9IH8tjaIJQtlxBHbMMki+GwiKmqEz39XQ31/lP66J+ImW8ns4fqNomz UsQF/tcM4agbjsKMPMzFyFGZkS3VuFQzAcJ8CQiOsZ3vXubtsURP2S5RMu/kAPraJF1e2hXmH ETqngd6UdhGSgjrmhB4kJlDAOzpGH+tTG6BD1XWWCUhAtCfv/uvjP9dd/6/uUI/CSZyjGoBmm 6IGxZwoEbjjN37KJMcr9fp+ovJ3AftZz0zIeO0FBPwOiLUKqxV/uq0Zsyez+MaauT111+R0Az eE5lNsUzyu8IMbiW5647R+0XfNgiWlCu80M+k88VKJ6+tB6a89Ry/+AZERbL4QY77v5PFfbgP ovbfGbKTNMKxRRIY+32Ii3DaBjGQ0OKLSuVD2kKW/X2bDOnXA4ur8yA6pSQhWbOtkzM/Q9wjp tZKTmEzX1piyHgSAkBkcyCwWbbfiniMOGnNc4ZGwQRAtDcAPHutAHeJdW7xqVwsc6Lhh6xxi9 2/Y+SCDUemZu4mbuLdaF0xU+9V3tnvtvhNwEsG6pBcONPWm4IjPMG92RHK8= X-Pg-Spam-Score: -4.9 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On Fri, May 05, 2017 at 09:25:04AM +0100, Gary Stainburn wrote: > This question has been asked a few times, and Google returns a few different > answers, but I am interested people's opinions and suggestions for the *best* > wat to retrieve the most recent row from a table. > > My case is: > > create table people ( > p_id serial primary key, > ...... > ); > > create table assessments ( > p_id int4 not null references people(p_id), > as_timestamp timestamp not null, > ...... > ); > > select p.*, (most recent) a.* > from people p, assessments a > .. > ; You will need to provide a definition for *exactly* what "most recent" means in this context. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql