[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