title: MySQL SELECT queries without LIMIT
body: <p>I am doing a course on Relational Databases, MySQL to be more especific. We need to create some SELECT queries for a project. The project is related to music. It has tables to represent musicians (musician), bands (band) and the musician ability to do a certain task, like singing or playing the guitar (act). </p>
  
  <p>Table <code>musician</code> contains : </p>
  
  <pre><code>id
  name
  stagename
  startyear
  </code></pre>
  
  <p>Table <code>band</code> contains :</p>
  
  <pre><code>code
  name
  type ("band" or "solo")
  startyear
  </code></pre>
  
  <p>And finally, table <code>act</code> contains :</p>
  
  <pre><code>band     (foreign key to code of "band" table)
  musician (foreign key to id of "musician" table)
  hability (guitarist, singer, like that... and a foreign key to another table)
  earnings
  </code></pre>
  
  <p>I have doubts in two exercises, the first one asks to select musicians <code>id</code> and <code>stagename</code> who participate with more acts in bands whose type is <code>solo</code>.</p>
  
  <p>My solution for the first one is this:</p>
  
  <pre><code>SELECT ma.id, ma.stagename
  FROM musician ma, act d, band ba
  WHERE ma.id = d.musician
  AND ba.code = d.band
  AND ba.type = "solo"
  GROUP BY ma.id, ma.stagename
  HAVING COUNT(ma.id) = (SELECT COUNT(d2.musician) AS count
                          FROM act d2, band ba2
                          WHERE d2.band = ba2.code
                          AND ba2.type = "solo"
                          GROUP BY d2.musician
                          ORDER BY count DESC
                          LIMIT 1);
  </code></pre>
  
  <p>The second one is very similar to the last one. We need to select, for every <code>startyear</code>, the <code>id</code> and <code>stagename</code> of a musician who can do more acts, with the corresponding number of acts and the maximum and minimum of his cachet. This is my solution:</p>
  
  <pre><code>SELECT ma.startyear, ma.id, ma.stagename, COUNT(ma.id) AS NumActs, MIN(d.earnings), MAX(d.earnings) 
  FROM musician ma, act d, band ba
  WHERE ma.id = d.musician
  AND ba.code = d.band
  AND ba.type = "solo"
  GROUP BY ma.year, ma.id, ma.stagename
  HAVING COUNT(ma.id) = (SELECT COUNT(d2.musician) AS count
                          FROM act d2, band ba2
                          WHERE d2.band = ba2.code
                          AND ba2.type = "solo"
                          GROUP BY d2.musician
                          ORDER BY count DESC
                          LIMIT 1);
  </code></pre>
  
  <p>The results with my dummy data are perfect but my teacher told us we should avoid using the <code>LIMIT</code> option, but that's the only way we can get the highest number, at least with what we know right now. </p>
  
  <p>I've seen a lot of subqueries after the <code>FROM</code> statement to solve this problem, however, for this project we can't use subqueries inside <code>FROM</code>. Is this really possible without <code>LIMIT</code> ?</p>
  
  <p>Thanks in advance.</p>