On Sun, Aug 24, 2008 at 6:18 PM, Susan Ottwell <[EMAIL PROTECTED]>wrote:
> How would one insert rows randomly within a range of 1000 rows? I can > easily enough do this in the script that calls the insert function, > but it would be more efficient if I could use an sqlite function or > feature to do this. > > sottwell There are a few ways to accomplish this, based on your particular definition of "random": 1. Unpredictable. This is where things get cryptographic. 2. Evenly distributed. 3. "Not obviously sequential". i.e. you don't really need unpredictable or even distribution, but you don't want your row IDs to go (1,2,3,4...) Anything that satisfies #2 will satisfy #3; anything that satisfies #1 will satisfy #2 (and by induction, #3). #1 is technically impossible, although it's possible to get fairly close. If you're on a *nix box, read 2-4 bytes out of /dev/random and treat them as an integer. #2 can be provided by a Mersenne Twister; most standard libraries' rand() functions are implemented using one. These two, while providing some semblance of randomness, have the disadvantage that you need to pick a number, then check to see if that number has already been used by another row. Thus I present a third option: #3 can be achieved through an LFSR (Linear Feedback Shift Register). A maximal n-bit LFSR will go through every numbers from 1 to (2**n)-1 without repeating, but do it in a seemingly random order. It's great if you want to e.g. generate what *looks* like a list of account numbers for a mock-up report/screenshot. Now, with that said, random rowids means poor locality of reference for newly inserted rows. This means cache misses and reduced performance. Why, exactly, do you want to randomize the rowid? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
