Dino Vliet <dino_vliet@yahoo.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
andthe 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
inSAS 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
