Hi, Looking at your problem I can think of one solution for small and *midsize* result sets. (And I have to say it may be similar to what Aleksander proposes). Write workaround query in the following form:
select addfield from ( select addfield, generated_counter from table where id = 2 union select addfield, generated_counter from table where id = 4 union select addfield, generated_counter from table where id = 1 ... select addfield, generated_counter from table where id = 3 ) order by generated_counter; I am not sure but that generated counter could be taken from DB sequence (something like select seq.nextvalue() from dual) or generated in Java while you compose the SQL statement. Well, this is what I have on my mind while I've just got up and my brain is still booting... Lukas On 7/3/06, Monsur Hossain <[EMAIL PROTECTED]> wrote:
On 6/30/06, Dominik Bruhn <[EMAIL PROTECTED]> wrote: > SELECT id,addfield FROM table WHERE id IN ([LUCENERESULT]); > > Where LUCENERESULT is like 2,3,19,3,5. > > This works fine but got one problem: The Search-Result of Lucene is order by > relevance and so the id-list is also sorted by relevance. But the result of > the SQL-Query is sorted by the id which destroys the relevance-sorting. > > Does anybody know a work-arround? We have the same issue and solve this through code. As you're generating the list of IDs for the SQL query, also generate a hash that maps the ID to its position. When reading back the data from the database, read the position hash, and insert the item into the appropriate position in an array. Yeah, you're doing the sorting yourself, but in our case, we're only returning 10 items per page, so its not a huge performance hit. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
