How can I write an if else-if else query in MySQL when returning variable number of rows

Question

I have three columns in a table:

score  status  No.
1,     2,      1
0,     1,      2
0,     0,      1

I need this, to write a C# style pseudo SQL:

rows = empty;

rows = "SELECT * FROM `table` WHERE score = 1"
if (rows.Count > 0) //at least one row
    return rows;

rows = "SELECT * FROM `table` WHERE status = 2"
if (rows.Count > 1) //more than one row 
    return row with MAX(No.) from rows; //ie MAX(No.) where status = 2

 return rows;

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).

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..

Edit: 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 SELECT * FROM table WHERE score = 1 will return just one row for now. And I need / I'll accept answers 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 score = 1. That is why I went for records instead of record. But ideally the business logic is to have all records with score = 1. 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.

Final Update: 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:

1. Answers which worked always: @GordonLinoff's, @ZaneBiens's, @ZaneBien's another, @Scen's, @JulienCh.'s (the last 3 being essentially the same, but yet I am not fully aware how those worked :))

2. Answers which worked only when first condition score = 1 returned only one row: @HannoBinder's, @ShlomiNoach's, @DaniellePaquette-Harvey's. 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)

3. Rest of the answers, I couldn't test as they were either not very specific or not related to MySQL.

4. @MatthewPK's is not appropriate in the context.

Awarding the bounty and accepting an answer is tough with so many right answers. I chose this one since I felt it is probably more efficient and readable too, but I'll accept @Scen's answer for being downright simple.

Answer 1

This seems hard, since you are returning a variable number of rows in the two cases.

The following query combines all possible rows, and then check on the presence of the first type to see which type should be returned.

select *
from ((SELECT 'SCORE' as matchtype, t.*
       FROM `table` t
       WHERE score = 1
      ) union all
      (SELECT 'STATUS' as matchtype, t.*
       FROM `table` t join
            (select max(`No.`) as maxno
             from `table` 
             WHERE status = 2
           ) tsum
           on t.`No.` = tsum.maxno
       WHERE status = 2
      )
     ) t cross join
     (select count(*) as cnt
      from `table`
      where score = 1
     ) const
where matchtype = (case when const.cnt > 0 then 'SCORE' else 'STATUS' end)

Note: the return set includes the MatchType. To get rid of this, you would need to include the full list of desired columns.

Answer 2

This query will solve your question:

SELECT *
  FROM `your_table`
WHERE
  status = 2 OR score = 1 
ORDER BY
  if(score = 1, 1, 0) DESC, 
  if(status = 2, 1, 0) DESC,
  `No.` DESC
LIMIT 1;

As opposed to other answers:

• It does not assume positive values for the column No.
• This is MySQL syntax (you have tagged as mysql)

But do feel free to give the credit/reputation to KSiimson, who laid out the right path. His query has two bugs, unfortunately.

Also note that said query cannot utilize any index on the ORDER BY clause, since we wrap columns with functions. Indexes are unlikely to be used on the FROM clause because of the OR condition.