CODEWITHSUNDEEP
mysql
Danial212k
Danial212k

Reputation: 130

How to select random limited rows providing number of rows in where clause based on condition of your choice

this is my table structure:

id | Risk

1  | Low
2  | Low
3  | High
4  | Low
5  | High
6  | High
7  | High
8  | Low
9  | High

Now I know that there are total 4 Low and 5 High.

What I want to achieve is to get 5 random rows where 3 rows from 'Low' and 2 rows from 'High'

This is my try:

SELECT *, 
(Select risk from myTable where risk = "Low" LIMIT 3) as lowRisk, 
(Select risk from myTable where risk = "High" LIMIT 2) as highRisk 
FROM myTable 
ORDER BY rand() 
LIMIT 5

I know subquery should return 1 row. Again I want something like this and this is my try code to explain you what I mean.

I am searching solution from 2 days.

Any help would be appreciated.

Upvotes: 0

Views: 23

Answers (1)

Akina
Akina

Reputation: 42651

(SELECT * FROM table WHERE Risk = 'Low' ORDER BY RAND() LIMIT 3) 
UNION ALL
(SELECT * FROM table WHERE Risk = 'High' ORDER BY RAND() LIMIT 2) 
-- uncomment if needed
-- ORDER BY RAND()

Study MySQL 8.0 Reference Manual / ... / UNION Clause

Pay attention - if there is no specified (in LIMIT) amount of rows for some Risk value then total amount of rows in the output will be less than 5.

Upvotes: 2

Related Questions