Re: last and/or first in a by group

From:
"Thomas Kellerer" <spam_eater(at)gmx(dot)net>
To:
"" <pgsql-general(at)postgresql(dot)org>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

****************

Thanks for your answer and if I look at it from a functionality point of view, this does the trick.

However, my table t1 (and hence t2) contains 6 million records AND I'm planning to do this repeatedly (with a scripting language for various moments in time) so this will end up being a very slow solution.

How can I speed these kind of queries up? By using indices, but on what columns would that be the best way then?
Or by trying to do this one time by constructing a table with the relevant information which can be used in such a way that I join thing in stead of using this subquery construction.

Thanks
Dino