Re: last and/or first in a by group

Dino Vliet <dino_vliet(at)yahoo(dot)com> 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