I have an idea for an aggregate function (actually a pair) that would be very useful. It's something I've wanted very frequently with Oracle and other databases and while it's possible to implement in SQL it's hard to do efficiently. Whereas it would be really easy for the database to do it efficiently.
lookup_min(column1,column2) lookup_max(column1,column2) would return the value of column2 (or one of the values in the case of duplicates) where column1 is the minimum/maximum value. Ie, it would have an accumulator that stores two values, the minimum/maximum value found so far, and the value of column2 for that record. So it would be possible to say for example: select min(column1),lookup_min(column1,column2) from tab to do the equivalent of: select column1,column2 where column1=(select min(column1) from tab) limit 1 except it would be way more efficient. (Especially if there's an index on column1 and postgres were taught to use indexes for min/max, but that's a different story.) I'm not sure on the names, perhaps someone has a better idea? I would be interested in doing this myself, it sounds like a fairly straightforward thing to implement and would be a useful first project. However I'm really a bit bewildered by the number of steps aggregate functions seem to have to go through to store accumulator data. It seems like they're going to a lot of effort to store the accumulator data in a database internal data-type. Is there something I can read to catch up on what these data structures are for and how to use them? -- greg ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org