[thelist] sql query - how to evenly distributed results

Bob Meetin ontheroad at frii.com
Wed Feb 28 11:28:09 CST 2007


Rudy, sorry but I am having trouble getting this to work.  Here is a 
little more info about the 2 tables:

galleries:
  gallery_id
  gallery_title (etc...)

images:
  image_id
  image_gallery_id
  image_adddate
  image_title (etc...)
  image_description (etc.)

and yes --> images.image_gallery_id = galleries.gallery_id

It's not important that I grab the last added images (image_adddate), 
but a nice bonus.  My knowledge falls short with inner/outer joins.  Is 
"a" a temp table?

-Bob


r937 wrote:
> latest 4 images per gallery --
>
> select a.gallery_id
>      , b1.image_id
>      , b1.image_date_added
>   from galleries as a
> inner
>   join images as b1
>     on a.gallery_id = b1.image_gallery_id
> inner
>   join images as b2
>     on a.gallery_id = b2.image_gallery_id
>    and b1.image_date_added <= b2.image_date_added
>  where galleries.gallery_id in ( 1, 2 )
> group
>     by a.gallery_id
>      , b1.image_id
>      , b1.image_date_added
> having count(*) <= 4  
> order
>     by a.gallery_id
>      , b1.image_date_added desc
>
> rudy
> http://r937.com/
>
>
>   





More information about the thelist mailing list