Dino Vliet <dino_vliet@xxxxxxxxx> wrote: > I want to know if postgresql has facilities for getting the first and or the last in a by group. > Suppose I have the following table: > resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival station, the class of the reservation and the daysbeforedeparture and records like: > xxx,NYC,BRA,C,80 > xxx,NYC,BRA,M,75 > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,Z,40 > zzz,NYC,LIS,J,39 > I want to select only the most recent records being: > xxx,NYC,BRA,Q,50 > yyy,WAS,LIS,T,55 > zzz,NYC,LIS,J,39 > How would you accomplish this? > I googled and found this: > http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggregate-Fun-Whos-on-First-and-Whos-on-Last.html > I hope there are alternatives because I don't have to program this myself. The other option is that I load this data in SAS and do it there but I only have SAS at work and want to do this at home. You can either use window functions in PostgreSQL 8.4 (cf. "FIRST_VALUE() OVER (...)"/"LAST_VALUE() OVER (...)") or use the "DISTINCT ON" syntax: | tim=# SELECT DISTINCT ON (resnr) resnr, dep, arr, cls, dbd FROM TestTable ORDER BY resnr, dbd; | resnr | dep | arr | cls | dbd | -------+-----+-----+-----+----- | xxx | NYC | BRA | Q | 50 | yyy | WAS | LIS | T | 55 | zzz | NYC | LIS | J | 39 | (3 Zeilen) | tim=# Tim -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
