Select equal number of records for the WHERE conditions

user_10436398 0
Viewed 135

I have a database and over one million records in it. I want to get the same number of records from the database for 3 different conditions. I am currently using code that performs 3 MySQL queries. I wonder if there is a possibility to get the same result using one SELECT query?.

The following code select an equal number of 20 records for each site=X condition.

<?php
/*---------*/
$db1 = mysqli_query($connect, "SELECT * FROM news WHERE site='1' ORDER BY id DESC LIMIT 0,20");

$db2 = mysqli_query($connect, "SELECT * FROM news WHERE site='2' ORDER BY id DESC LIMIT 0,20");

$db3 = mysqli_query($connect, "SELECT * FROM news WHERE site='3' ORDER BY id DESC LIMIT 0,20");

/*------------*/

?>
mysqli mysql sql php
1 Answers

You can use UNION ALL to combine results of multiple Select queries. We need to ensure that the number of columns being returned are same in all the Select queries.

(SELECT * FROM news WHERE site='1' ORDER BY id DESC LIMIT 0,20)
UNION ALL
(SELECT * FROM news WHERE site='2' ORDER BY id DESC LIMIT 0,20)
UNION ALL
(SELECT * FROM news WHERE site='3' ORDER BY id DESC LIMIT 0,20)

Now, always remember that the result of the UNION will always be an Unordered dataset. In case, you intend the final results to be sorted by id; you can add an explicit Order By at the end. It is very well discussed in the Docs:

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

user_24693080
Related