[thelist] sql query - how to evenly distributed results

Luther, Ron Ron.Luther at hp.com
Mon Feb 26 08:44:25 CST 2007


Bob Meetin asked:


>>mysql - 2 tables

>>$query = "select * from galleries, images where galleries.gallery_id 
>>= images.image_gallery_id and galleries.gallery_id in ('1','2') limit
8";


Hi Bob,

Ummmm ... Sorry, but this really looks like a 1 table query to me.  I'm
not 
seeing any effective difference between the above and:

Select * from images where image_gallery_id in ('1','2') limit 8;


>>this returns 8 rows which is fine but what I want to do is to evenly 
>>distribute 4 from each gallery, so 4 image from gallery #1 and 4 from 
>>gallery #2. i can easily do this but running a nested query, but just 
>>now i can't think how to do this in a single query, perhaps something 
>>to do with group by?

Okay.  I'm kinda scratching my head on why you're not getting the same 8

images every time.  Must have some neat resorting, reordering,
reindexing 
process going on behind the scenes, eh?  I would think you would want
the 
images pulled at random.


Here's one technique (of many) to let you do that.

Step 1: Let's say you have some process which renumbers and 'packs' the 
images through insertions and deletions so that you know that at this 
point in time you have 32 images in gallery '1' and you could associate 
each image with an integer between 1 and 32. (or 0 and 31 if you're into

that sort of thing.).  Don't really care if you are doing that for your 
image_id in your primary image table or if you are maintaining a
separate 
table associating this autonumbering to your image_ids. Doesn't matter.

Step 2: In PHP, ASP, ESP, or whatever you happen to be working in, 
select 4 random numbers from the range of images available for gallery 1

and 4 random numbers from the range of images available for gallery 2. 
Prepend a '1' to the random numbers to come from gallery '1', a '2' for 
the images to come from gallery '2', etc.

Step 3: Now bring back the images.  (Caveat: I don't work with mysql
enough 
to know if you can work with compound variables in the way I am assuming
below 
or if you need to add a column to the table structure to do that.
Either way 
works.)

Select * from images where (image_gallery_id [appended using '.' or '&'
or 
whatever to] image_id) in ('list of generated random numbers with
gallery ids 
prepended');

{You may need to make some small adjustments to the above select to
cover 'type' 
considerations. May need to toss in some 'convert to number' function. I
don't 
remember how much of that mysql automagically covers for you.}


HTH,
RonL.



More information about the thelist mailing list