How could one go about writing a query that returns a list of records in the order their IDs were given?

The query "select * from foo where id in (38, 13, 2)" would return those records but in the order 2, 13, 38. This isn't what I want.

Clearly I could loop over my IDs and perform a single fetch for each but this would be inefficient. Any suggestions?

what about order by usage.

If you need only the pk

You should have a look in the Hibernate reference to find more information.

Regards Sebastian

I don't think what you want is possible in SQL. Try fetching them in a single db hit and then ordering them in memory.


Regards
Brian

I think your right. I guess at the end of the day there is a trade of between ordering yourself or a select for each item. Given that we may page the list I'm inclined to go with the fetch for each record as there'll be a maximum of 50 or so fetches each time.

To put this into context, the ID's are coming from a Lucene search where the results are (obviously) weighted. The database behaviour I'd like to avoid renders the weighting useless. I know I could avoid fetching the rows from the database and build the results from my Lucene hits but it's good to verify the data you're getting back is valid and the results do actually exist. In a perfect world I wouldn't need to bother, I'm just covering myself.