 | |
Random rows with preserved order after insert/delete
comp.databases.mysql comp.databases.mysql
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Random rows with preserved order after insert/delete -
10-03-2010
, 01:25 PM
|
On 2010-09-29 09:57, Willem Bogaerts wrote: - Handle it in code. I don't know what the problem is, but my gut feeling says that is the most logical way to go if your problem involves a "working set". - Create a temporary table based on the SELECT statement. You won't get a random insertion point, but you can solve that by adding an extra field with a position value to the temporary table. - Don't create a temporary table, but create a (unique) position column in the original table. Upon insertion, fill it with a random number. If you get a uniqueness violation, try again with a different random position. Your SELECT query would then be modified to order by the new position column. This is probably the best option if your problem is a permanent randomization like in, for instance, medical trials. Good luck, Thanks for the input Willem. The thing is that since I cannot modify the table or the database I have to store the position information elsewhere. This is a table with some five millions rows so a serialized binary tree with the positions as keys is thinkable. |
ordering information plus keys to the appropriate rows in the main
table. If you can't change the database *at all*, then your database
doesn't support what you want to do alone and you'll have to manage it
at the application layer. And that's pretty much the end of the line in
that direction.
--
22. No matter how tempted I am with the prospect of unlimited power, I
will not consume any energy field bigger than my head.
--Peter Anspach's list of things to do as an Evil Overlord
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.