CODEWITHSUNDEEP
mysqlsqlwordpress
amitdutt24
amitdutt24

Reputation: 67

MySql Query to get certain number of values from limit and order by rand()

In below SQL query I need 25 rows containing product = 'y' and 25 rows having product = 'n'. Product is a column having Y/N value only. In limit of random 50 rows I need 25 rows from each values. 

SELECT qno 
FROM `wp_question_bank` 
where brand='XYZ' 
and department='DOMESTIC' 
and product = 'y' 
and priority = 'p1' 
ORDER BY RAND() limit 50

Kindly assist. I have tried Union but it didn't work.

Upvotes: 0

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you have enough rows of each type, you can do this without a subquery:

SELECT qno
FROM `wp_question_bank`
WHERE brand = 'XYZ' AND department = 'DOMESTIC' AND
      product IN ('n', 'y') AND priority = 'p1'
ORDER BY ROW_NUMBER() OVER (PARTITION BY product ORDER BY rand())
LIMIT 50;

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

Simply use UNION for each product type:

(SELECT qno
FROM `wp_question_bank`
WHERE brand='XYZ' AND department='DOMESTIC' AND product = 'y' AND priority = 'p1'
ORDER BY RAND()
LIMIT 25)

UNION ALL

(SELECT qno
FROM `wp_question_bank`
WHERE brand='XYZ' AND department='DOMESTIC' AND product = 'n' AND priority = 'p1'
ORDER BY RAND()
LIMIT 25)

Upvotes: 1

GMB
GMB

Reputation: 222442

You could use row_number() if you are running MySQL 8.0:

select qno
from (
    select qno, row_number() over(partition by product order by rand()) rn
    from wp_question_bank
    where brand='XYZ' and department='DOMESTIC' and priority = 'p1'
) t
where rn <= 25

Upvotes: 0

Related Questions