Nathan Kurz wrote:
Perhaps related to the recent questions about converting rows to
columns, I'm finding the need for user defined aggregate functions
that can return multiple values, or ideally multiple rows of values.
Assume you to determine the highest N values from column. You'd want
a aggregate function like max_n(col, n) that makes one pass through
the table, saving the highest values it sees, and then returning them.
SELECT max_n(col, 3) FROM test;
max
---
10
9
8
My current workaround is to have my function return a comma separated
list of values ("10,9,8"), parse this string in my application, and
generate a new query, but ideally I'd like to do this in one step.
Is there any reasonable way to accomplish this? Or am I left with
defining a new function type that returns a handle to a temp table,
and new parsing logic to wrap the right OP codes around that function?
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
Nathan,
I don't know of a way to do what you want with a user defined function,
but your example can be solved quite simply using SQL. The following
query will return a table with the required results.
select * from test order by col desc limit 3;
If you have an index on col then it will also be very fast regardless of
the size of the table, if not it will do a single table scan to find
the three maximum values.
HTH
Dennis Cote
