Your question is so confusing that I am going to assume there is something you have not been able to express in the asking of it --
On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote:
Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants.
what do you mean by "which row"? Do you want to know the position of the required row within your result set, sort of like i = 0 foreach row if currentrow's memberid == 4567373 print i get out of the loop else i++ Well, you already know how to do that. Do you want to know the position of that row in your database? There really is no such thing, at least not in a reliable way. A database stores stuff. You take the stuff out, and you impose an order over it. If you don't impose any order, it returns stuff normally in the order of some index, usually the primary key. If your data are reliably numbered from, say, 1, sequentially incrementing, AND if you have never deleted any row, then memberid 4567373 will be row number 4567373. If you simply want row with memberid = 4567373, well, just SELECT * FROM table WHERE memberid = 4567373; But then, maybe you are really wanting to ask something else that I haven't understood.
For example, imagine statement X which returns a column of memberid. They can be ordered by first name, last name, date, etc. If I want to find out where (which row) memberid 4567373 stands in the result of statement X, what is the most efficient way? Currently, the best way I can think of is: "SELECT memberid from (X)" and bring all of the results into my application and then iterate through all of the results in a for loop, looking for the memberid 4567373. It seems inefficient. Is their a better way? It would be nice if there was some kind of statement that could return where a specific memberid was in the results of another statement. Thanks, Gilles
-- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
