如何使用特定属性查询至少X个结果

Question

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?

Answer 1

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.