mysql group by uid which uid match another query

Question

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?

select * from article_table where image!='' 
order by rand() 
group by uid 
in (select uid from user_table where type='1') 
limit 10

score 2 · Accepted Answer · edited May 23 '17 at 12:03

I'd do it with a join instead

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

Or you may want to restrict the number of uids from your user_table to make it quicker to begin with:

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

I'm assuming here that there are many articles to each user. Although it looks more horrible, 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.

Be careful, ordering by a random value can incur a significant performance hit as you have to go through the entire table that matches your where clause. There are alternatives.

score 1 · Answer 2 · answered Mar 04 '12 at 12:34

This following query will do it,

SELECT * 
FROM   article_table 
WHERE  image!='' 
       AND uid IN (SELECT DISTINCT uid 
                   FROM   user_table 
                   WHERE  TYPE = '1' 
                   LIMIT  10) 
GROUP  BY uid 
ORDER  BY Rand() 
LIMIT  10

mysql group by uid which uid match another query

2 Answers2