CODEWITHSUNDEEP
mysqlsql
Mik
Mik

Reputation: 105

Select n random groups of n rows

I have this table services, with data as follows:

service_id  bepro_id    service_name
        1   1   Virtuales
        2   2   Logos1
        3   3   Diseño C
        4   1   Formatos A3
        203 77  Dasdad
        6   2   Publi
        8   4   edificios Vi
        9   3   Maquillaje D
        10  7   Servicio 10 Serv
        11  4   servicio 11 tittle
        14  3   Lorem ipsum dol
        15  30  servicio 15 title
        16  4   Titulo TituloTitulo
        17  8   servicio 17 title ñ
        19  11  Retoques digital 
        20  4   servicio 20 title
        26  5   service 26 title
        27  7   servicio 27 tittle
        183 3   Excepteur sint occaecat 
        188 3    Duis aute irure dolor 
        185 3   Cillum dolore eu fugiat
        186 3   Eserunt mollit anim i
        190 3   Lorem ipsum dolor
        191 3   general para ed
        192 3   Ingenieria
        193 3   letras
        194 2   bocetos
        196 60  Retoque fotografico
        199 60  Maquetacion de 
        198 28  Revistas en General

I want to select from 8 distinct random values in bepro_id 1 to 8 per each values (minimun 1 ,maximun 8) each bepro_id.

I mean maximum 64 rows or minimum 8 rows. Then I have to JOIN with table "users" where services.bepro_id=users.users_id .

This selects only one random row per value ( I need min 1 max 8 values for each value) below @Andomar help me a lot but doesn't seems to work and it look complicated for what it is , is that the only way i could do?:

SELECT * FROM  `services` GROUP BY bepro_id ORDER BY RAND( ) LIMIT 8

Thank you in advance

Upvotes: 2

Views: 175

Answers (1)

Andomar
Andomar

Reputation: 238126

You could use the MySQL variable trick to label each row with a row number. An inner join can be used to limit the result to N bepro's. The example below returns 1-2 random services for 3 random bepro's. It should be easy to change those values.

This is what i need thank to @Andomar :

select  *
from    (
        select  distinct bepro_id
        from    services
        order by
                rand()
        limit   8 -- Eight random bepro_id's
        ) bepro
join    (
        select  if(@last_bepro = bepro_id, @rn := @rn + 1, @rn := 1) as rn
        ,       (@last_bepro := bepro_id)
        ,       service_id
        ,       bepro_id
        ,       service_name
        from    services
        cross join
                (select @rn := 0, @last_bepro := -1) r
        order by
                bepro_id
        ) serv
on      serv.bepro_id = bepro.bepro_id
join    users 
on      users.id = bepro.bepro.id
where   serv.rn <= 8 -- 8 services max , 1 min per bepro_id
order by
        rand();

Live example at SQL Fiddle.

Upvotes: 2

Related Questions