On Fri, Nov 18, 2005 at 04:25:12PM -0700, Dennis Cote wrote: > >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? > > 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.
Thanks Dennis. Unfortunately, I was only using the max() function as a simplified case to try to show what I wanted. In reality, I need to try to find which which N rows are most similar to a given row in the table based on a custom vector similarity function that compares a blob in the test field against a blob in each of the other rows (as well as some joined in parameters). So a precalculated index is out. Also, I think the part that Igor was pointing out was the "if not it will do a single table scan". His point (which I think is true) is that in if no index is available with the information then a temporary copy of the entire table has to be created and sorted. The 'complex heap approach' is indeed what I'm doing in my user defined aggegate function, as the cost of the temp table creation and sorting is the reason I can't do this as a simple function with an 'ORDER BY'. So I'm still looking for how it could be done with user defined functions, even if that means significantly extending the way that user defined functions are handled in SQLite. Advice on how to do that (or suggestions on better alternatives) greatly appreciated. --nate
