Mysql - select random 4 data from a column, distinct by 2 different columns

139
December 26, 2017, at 7:06 PM

I'm trying to select 4 different random data from a table on Mysql but I want some fields unique, for example;

I've got a table

Table name 'videos' and the data is ;

id f2  f3  f4
-- --  --  --
1  a   q   C  
2  a   w   Y
3  b   e   C
4  b   r   Y
5  c   t   C
6  c   y   Y
7  d   u   C
8  d   o   Y

I want to select 4 data randomly from f3 but f2 must be Unique. And the f4 must be unique for every f2, I mean I must get randomly 'u' or 'o' not both. So in the end I want to get 2xC data column from unique f2, and 2xY data from unique f2. Result I want to get is like;

f3      f3        f3
--      --        --
q   or|  q   or|  w
r   or|  e   or|  r
t   or|  y   or|  t
o   or|  o   or|  u

Here's a sample that I created in MsSql but cant convert it to Mysql;

select e.* from (
      select top 4 f2,
             ROW_NUMBER() OVER (ORDER BY newId()) AS RowNumber 
      from (
         select distinct(f2) from videos
      ) x
) a inner join (
      select top 4 ones.n,
             ROW_NUMBER() OVER (ORDER BY newId()) AS RowNumber 
      FROM (VALUES('C'),('Y'),('C'),('Y')) ones(n)
) b on a.RowNumber = b.RowNumber
inner join videos e on a.f2 = e.f2 and b.n =e.f4
Answer 1

What about to sort randomly data then use mysql ONLY_FULL_GROUP_BY default behaviour ( disabled ):

This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

SQL Fiddle

MySQL 5.6 Schema Setup:

create table t ( id int, f2 char, f3 char, f4 char );
insert into t values
(1  ,'a'   ,'q'   ,'C'),
(2  ,'a'   ,'w'   ,'Y'),
(3  ,'b'   ,'e'   ,'C'),
(4  ,'b'   ,'r'   ,'Y'),
(5  ,'c'   ,'t'   ,'C'),
(6  ,'c'   ,'y'   ,'Y'),
(7  ,'d'   ,'u'   ,'C'),
(8  ,'d'   ,'o'   ,'Y');

Query 1:

select  f2, f3, f4
from (
 select f2, f3, f4
 from (
  select f2, f4, f3 from
   ( select f2, f4, f3
     from t
     order by rand()
   ) t0
  group by f2
 ) t1  
 order by RAND() 
) t2
group by f2, f4  

Results:

| f2 | f3 | f4 |
|----|----|----|
|  a |  w |  Y |
|  b |  r |  Y |
|  c |  t |  C |
|  d |  o |  Y |
READ ALSO
Disable PAY Button when status “paid”

Disable PAY Button when status “paid”

I want to disable pay button when status paid

141
MySQL Select the field as Distinct having data type as Text with filtering

MySQL Select the field as Distinct having data type as Text with filtering

If we have a table T1 with a field named orders have data type as Text

130
Mysql Optimization suggestion for large table

Mysql Optimization suggestion for large table

i want to optimize this query,

122
Script for tacking mysql backup in ubuntu 16

Script for tacking mysql backup in ubuntu 16

Plz help me to write the script in ubuntu for tacking mysql backup,I have already written few line in the scriptsh file for tacking mysql dump and run this script from crontab, but it is not working

213