title: How can I write an if else-if else query in MySQL when returning variable number of rows
body: <p>I have three columns in a table:</p>
  
  <pre><code>score  status  No.
  1,     2,      1
  0,     1,      2
  0,     0,      1
  </code></pre>
  
  <p>I need this, to write a C# style pseudo SQL:    </p>
  
  <pre><code>rows = empty;
  
  rows = "SELECT * FROM `table` WHERE score = 1"
  if (rows.Count &gt; 0) //at least one row
      return rows;
  
  rows = "SELECT * FROM `table` WHERE status = 2"
  if (rows.Count &gt; 1) //more than one row 
      return row with MAX(No.) from rows; //ie MAX(No.) where status = 2
  
   return rows;
  </code></pre>
  
  <p>I hope I could be clear. In short, select from my table records with score = 1, and if there isn't such a record, return the record where status = 2 and if there are more than one record with status = 2, then return the record with maximum value for No. where status = 2 (if no record at all with status = 2, return empty). </p>
  
  <p>How can I write it in one query? It should be a good learning experience for me. Otherwise I know to breakup into smaller queries and run each one. And I can't go with stored procedures right now..</p>
  
  <p><strong>Edit:</strong> Actually my query will have a few more WHERE clauses but identical in both the conditions and that is why I omitted it. Hence, regarding the first condition, there will be only one record returned for now. That is <code>SELECT * FROM table WHERE score = 1</code> will return just one row for now. And <strong>I need / I'll accept answers</strong> that gives such a solution too. But the point is you never know, may be in future with some design changes, there could be more rows with <code>score = 1</code>. That is why I went for <strong>records</strong> instead of <strong>record</strong>. But ideally the business logic is to have all records with <code>score = 1</code>. For now, record will do. I'm just thinking query will be much simpler if only one row is returned and my teammates can assimilate the code easily.</p>
  
  <p><strong>Final Update</strong>: Thank you all guys, you've been very kind :) Many answers worked well, and choosing one is really really daunting. My finding on the answers:</p>
  
  <ol>
  <li><p>Answers which worked always: <a href="https://stackoverflow.com/a/11417683/661933">@GordonLinoff's</a>, <a href="https://stackoverflow.com/a/11511648/661933">@ZaneBiens's</a>, <a href="https://stackoverflow.com/a/11446343/661933">@ZaneBien's another</a>, <a href="https://stackoverflow.com/a/11512028/661933">@Scen's</a>, <a href="https://stackoverflow.com/a/11404254/661933">@JulienCh.'s</a> (the last 3 being essentially the same, but yet I am not fully aware how those worked :))</p></li>
  <li><p>Answers which worked only when first condition <code>score = 1</code> returned only one row: <a href="https://stackoverflow.com/a/11509577/661933">@HannoBinder's</a>, <a href="https://stackoverflow.com/a/11412137/661933">@ShlomiNoach's</a>, <a href="https://stackoverflow.com/a/11459076/661933">@DaniellePaquette-Harvey's</a>. For the moment, I 'll stick with @Danielle's (which is freakingly simple) and later revert if there arises need to have more than one row) </p></li>
  <li><p>Rest of the answers, I couldn't test as they were either not very specific or not related to MySQL.</p></li>
  <li><p><a href="https://stackoverflow.com/a/7680027/661933">@MatthewPK's</a> is not appropriate in the context.</p></li>
  </ol>
  
  <p>Awarding the bounty and accepting an answer is tough with so many right answers. I chose <a href="https://stackoverflow.com/a/11511648/661933">this one</a> since I felt it is probably more efficient and readable too, but I'll accept @Scen's <a href="https://stackoverflow.com/a/11512028/661933">answer</a> for being downright simple.</p>