From: "Thomas Kellerer" <spam_ea...@gmx.net> To: "" <pgsql-general@postgresql.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