The simplest solution to this I would suggest is to decode the id to relevance score e.g.
Select id, addfield >From mytable Where id in (1,2,3,4,5,50,60,70) Order by case id when 1 then 0.9 when 2 then 0.8 when 3 then 0.7 .... end desc You will have to generate the in () and the case statement but this will do it. It will still go pear shaped when two ids have the same score unless you fiddle this. Mike www.ardentia.com the home of NetSearch -----Original Message----- From: Lukas Vlcek [mailto:[EMAIL PROTECTED] Sent: 04 July 2006 05:54 To: java-user@lucene.apache.org Subject: Re: Sorting & SQL-Database 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]