Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id E8376A684DE
	for <pgsql-sql@postgresql.org>; Mon, 21 May 2012 13:05:08 -0300 (ADT)
Received: from mailout-de.gmx.net ([213.165.64.23])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <maps.on@gmx.net>) id 1SWV6U-0001Zv-IE
	for pgsql-sql@postgresql.org; Mon, 21 May 2012 16:05:07 +0000
Received: (qmail invoked by alias); 21 May 2012 16:04:49 -0000
Received: from mue-88-130-16-026.dsl.tropolys.de (EHLO [192.168.1.113])
	[88.130.16.26]
	by mail.gmx.net (mp031) with SMTP; 21 May 2012 18:04:49 +0200
X-Authenticated: #14269776
X-Provags-ID: V01U2FsdGVkX1+GJ35XtKiGAwHlT/j6622Tt+/2nAac7dvh5q1FKd
	H3ZgjC4VtIgQXW
Message-ID: <4FBA67A7.4010302@gmx.net>
Date: Mon, 21 May 2012 18:04:55 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Select every first/last record of a partition?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Y-GMX-Trusted: 0
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201205/58
X-Sequence-Number: 36606

Hi,

suppose a table that has records with some ID and a timestamp.

id,    ts
3,    2012/01/03
5,    2012/01/05
7,    2012/01/07
3,    2012/02/03
3,    2012/01/05
5,    2012/03/01
7,    2012/04/04

to fetch every last row of those IDs I do:

select   id, ts
from   (   select   id, ts, row_number() over ( partition by id   order 
by ts desc ) as nr from mytab ) as x
where nr = 1


Is there a another way without a subselect?
There might be more columns so the window-functions first/last won't help.