I am trying to get a certain amount of rows of which another amount of rows satisfy a specific condition.
I'll explain.
table 1:
ID | NAME
1 | Thomas
2 | Jason
3 | Oleg
4 | Matt
5 | Sheldon
6 | Jenny
table 2:
ID | ACTIVE
1 | 1
2 | 0
3 | 1
4 | 1
5 | 0
6 | 1
Query:
SELECT tbl_1.ID, tbl_1.NAME, tbl_2.ACTIVE
FROM tbl_1 JOIN tbl_2 ON
tbl_1.ID = tbl_2.ID
WHERE tbl_2.ACTIVE=1
LIMIT 5
in this example I would like to get a minimum number of 5 users, of which 3 are active. of course the query above will not do the job right, as it limits the total rows to 5. But 3 of the rows in the result (or less if no more exist) MUST be active.
the other way I can think of getting this done, is a union, but my query is so cumbersome, long and complex.
Any ideas?
Use ORDER BY instead:
SELECT tbl_1.ID, tbl_1.NAME, tbl_2.ACTIVE
FROM tbl_1 JOIN
tbl_2
ON tbl_1.ID = tbl_2.ID
ORDER BY (tbl_2.ACTIVE = 1) DESC
LIMIT 5;
This puts the active users at the top of the list and then fills in the rest with other users.
Note: The ORDER BY clause could simply be ORDER BY tbl_2.ACTIVE DESC. I left the boolean logic so you could see the similarity to the WHERE clause.
The way to at least x results is to use the count aggregate and the keyword having
select f1, count(*) records
from yourTable
where whatever
group by f1
having count(*) > x