vendredi 7 juillet 2017

mysql select data from each combination in random order

I have a table named it_vit_qs from which i have to make a question set. Each question set should obtain qs from every category (e.g. cat_id) and every level (e.g lavel_id) in random order.

Note that each category(cat_id) have 3 level. There are 7 category. That means there will be 3*7= 21 combination. That means cat_id 1 have level_id 1,2 and 3, cat_id 2 have level_id 1,2 and 3 and so on.

Now how i can select 5 question from each cat & level combination in random order??? i have tried the below query but it seems like slow and bad practice. I have included my sql and its result. But I want to do it in efficient & easier way.

SELECT 
  a.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
  WHERE cat_id = 1 AND level_id=1
  ORDER BY RAND() 
  LIMIT 3) a 
UNION
SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 1 AND level_id=2
  ORDER BY RAND() 
  LIMIT 3) b 

  UNION

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 1 AND level_id=3
  ORDER BY RAND() 
  LIMIT 3) b

  UNION 

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 2 AND level_id=1
  ORDER BY RAND() 
  LIMIT 3) b

  UNION 

  SELECT 
  b.* 
FROM
  (SELECT 
    * 
  FROM
    it_vit_qs 
    WHERE cat_id = 2 AND level_id=2
  ORDER BY RAND() 
  LIMIT 3) b

The table image




Aucun commentaire:

Enregistrer un commentaire