Mysql query to select all rows with a certain value and 5 other rows without that value-CodePudding

I want a mysql query that selects all rows with a specified value on a column and 5 rows without that value.

Select * 
FROM myTable 
WHERE is_read=1 
OR (is_read=0 AND found_rows_of_this_value<5)

So if we have 100 rows with is_read=1 we get a result of 105 rows

and if we have 0 rows with is_read=1 we get a result of 5 rows.

CodePudding user response：

Use a UNION. UNION combines the result from multiple SELECT statements into a single result set. The "LIMIT 5" returns only 5 rows. U can change it for get more.

Select * 
FROM myTable 
WHERE is_read=1
UNION (
Select * 
FROM myTable 
WHERE is_read=0
LIMIT 5)