mysql group by uid which uid match another query

Mar 12, 2012   //   by phpfreelancer.biz   //   Blog, MySQL Tutorial  //  No Comments

I want get 10 rand result, which image !=”, group by uid and these uid are the same from select uid from user_table where type=’1′. but my query only return 2 result. where is the problem?

Here’s the code:

select at.*
  from article_table at
  join ( select uid
           from user_table
          where type = '1'
          order by rand()
          limit 10 ) ut
    on at.uid = ut.uid
 where image != ''
 group by at.uid
 order by rand()
 limit 10

the order by rand() in the inner select is over a smaller dataset, which’ll speed things up and the order by in the outer select only has to deal with a smaller number of rows.

Leave a comment

Share This Post

RSS Wordpress News