CODEWITHSUNDEEP
mysqlsqlunion
youanden
youanden

Reputation: 386

Efficient way to UNION based on inner query return count?

I had planned on asking another question, but after messing with SQL fiddle, I was able to solve my problem (can't attest to the efficiency, however).

http://sqlfiddle.com/#!2/75c1b/4

The purpose is to ask from a limited set the problems the user is "bad" at, and once those are exhausted, ask from a pool of problems never attempted.

Is there a more efficient way to organize that by way of a dynamic LIMIT based on inner query row count, or should I just use a LIMIT within each sub-query so as not to expend needless resources?

Note: The final outer query would never request beyond 30 rows.

Thanks to @Squirrel for showing me SQL Fiddle.

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If you want everything from the first subquery and then to fill out the rest from the second, then sort the result set and take a limit of the result:

SELECT problem_id, name
FROM ((SELECT problem_id, name, 'bad' as which
       FROM (SELECT r.problem_id, p.name, sum(r.value) as knowledge
             FROM responses r JOIN
                  problem p
                  ON p.id = r.problem_id
             GROUP BY r.problem_id
            ) 
       WHERE knowledge = 0
      ) union all
      (SELECT id, name, 'new'
       FROM problem p
       WHERE p.id NOT IN (SELECT problem_id FROM responses)
       GROUP BY p.id
      )
     ) as final
ORDER BY which
LIMIT 30;

Upvotes: 1

Related Questions