Re: last and/or first in a by group

[Thomas Kellerer <spam_eater@xxxxxxx>]

Dino Vliet wrote on 16.05.2010 18:07:
> Dear postgresql experts,
>
> 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

Something like this?

SELECT *
FROM your_table  t1
WHERE dbd = (SELECT min(dbd)
             FROM your_table t2
             WHERE t2.dep = t1.dep
               AND t2.arr = t1.arr
               AND t2.resnr = t1.resnr)


Regards
Thomas



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general